[20131121]12c sqlplus的set colinvisible on.txt

12c 加入invisible column,但是如果要查看表的隐含列,要查询一些视图.实际上在sqlplus执行set colinvisible on,在执行desc

做一个测试看看.

@ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SQL> create table t ( a number,b number,c number);
Table created.

SQL> desc t;
Name  Null?    Type
----- -------- --------
A              NUMBER
B              NUMBER
C              NUMBER

SQL> column column_name format a20
SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column from user_tab_cols where table_name = 'T';

 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME          HID VIR
---------- ----------------- ------------------ -------------------- --- ---
         3                 3                  3 C                    NO  NO
         2                 2                  2 B                    NO  NO
         1                 1                  1 A                    NO  NO

--如果隐藏列.
SQL> alter table t modify b invisible;
Table altered.

SQL> desc t
Name  Null?    Type
----- -------- ---------------------------
A              NUMBER
C              NUMBER

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column from user_tab_cols where table_name = 'T';

 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME          HID VIR
---------- ----------------- ------------------ -------------------- --- ---
         2                 3                  3 C                    NO  NO
                           2                  2 B                    YES NO
         1                 1                  1 A                    NO  NO

SQL> select column_id, column_name from user_tab_columns where table_name='T';

 COLUMN_ID COLUMN_NAME
---------- --------------------
         1 A
           B
         2 C        
--要查询对应的视图才知道.

SQL> set colinvisible on
SQL> desc t
Name           Null?    Type
-------------- -------- ----------
A                       NUMBER
C                       NUMBER
B (INVISIBLE)           NUMBER

--修改为可见.
SQL> alter table t modify b visible;
Table altered.

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column from user_tab_cols where table_name = 'T';

 COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME          HID VIR
---------- ----------------- ------------------ -------------------- --- ---
         2                 3                  3 C                    NO  NO
         3                 2                  2 B                    NO  NO
         1                 1                  1 A                    NO  NO

SQL> desc t
Name  Null?    Type
----- -------- -------
A              NUMBER
C              NUMBER
B              NUMBER

--可以发现显示顺序COLUMN_ID发生了变化,但是存储顺序 SEGMENT_COLUMN_ID并没有改变. INTERNAL_COLUMN_ID保持开始建立的顺序.