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

[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保持開始建立的順序.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-777179/，如需轉載，請註明出處，否則將追究法律責任。