[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保持开始建立的顺序.
相关热门文章
