http://www.anbob.com/archives/2083.html
Oracle 12c release 1版本引入新功能可隱藏列(invisible columns),這個特性有很多的爭議，不明白為什么ORACLE會推出這個特性，因為這個特性可能會帶來很多麻煩，列默認是顯示的可以通過create table 或alter table 控制此特性，下面我來演示一下此特性。
anbob@PDB1>create table test(id int,id1 int,id2 int,id3 int);Table created.anbob@PDB1>insert into test values(1,2,3,4);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) ID1 NUMBER(38) ID2 NUMBER(38) ID3 NUMBER(38) anbob@PDB1>select * from test; ID ID1 ID2 ID3-------------------- -------------------- -------------------- -------------------- 1 2 3 4anbob@PDB1>alter table test modify id2 invisible;Table altered.anbob@PDB1>select * from test; ID ID1 ID3-------------------- -------------------- -------------------- 1 2 4 anbob@PDB1>desc test; Name Null? Type ----------------------------- -------- -------------------- ID NUMBER(38) ID1 NUMBER(38) ID3 NUMBER(38)anbob@PDB1>alter table test modify id1 invisible;Table altered.anbob@PDB1>desc test; Name Null? Type ----------------------------- -------- -------------------- ID NUMBER(38) ID3 NUMBER(38) anbob@PDB1>insert into test values(11,12,13,14);insert into test values(11,12,13,14) *ERROR at line 1:ORA-00913: too many valuesanbob@PDB1>insert into test(id,id1,id2,id3) values(11,12,13,14);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>select * from test; ID ID3-------------------- -------------------- 1 4 11 14anbob@PDB1>select id,id1,id2,id3 from test; ID ID1 ID2 ID3-------------------- -------------------- -------------------- -------------------- 1 2 3 4 11 12 13 14note:從上面可以看出這個特性隱藏的也只是對于desc 和 select “*” 的顯示和未指定列名insert 時隱藏列無效, 如果把隱藏列重新顯示會有什么影響？接著下面演示
查看兩列隱藏后的建表DDL
anbob@PDB1>select dbms_metadata.get_ddl('TABLE','TEST') from dual;DBMS_METADATA.GET_DDL('TABLE','TEST')-------------------------------------------------------------------------------- CREATE TABLE "ANBOB"."TEST" ( "ID1" NUMBER(*,0) INVISIBLE, "ID2" NUMBER(*,0) INVISIBLE, "ID" NUMBER(*,0), "ID3" NUMBER(*,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT Flash_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"NOTE:也看出此時的建表語句列順序已經是變化的。
看一下col$基表的變化，col#是顯示的順序，在我以前的日志中有寫個修改列順序”oracle 修改列的前后順序”http://www.anbob.com/archives/757.html
tip:The data dictionary COL$ table contains one row for each table column. Each row has three column numbers:COL# – external column numberINTCOL# – internal column numberSEGCOL# – segment column number
-- 修改前，所有列正常顯示sys@ORA12C>select name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') from col$ where obj#=91771NAME COL# INTCOL# SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID 1 1 1 0ID1 2 2 2 0ID2 3 3 3 0ID3 4 4 4 0-- 修改后，隱藏ID1,ID2列sys@ORA12C>/NAME COL# INTCOL# SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID 1 1 1 0ID1 0 2 2 400000020ID2 0 3 3 400000020ID3 2 4 4 0note:注意修改隱藏列會把col#更新為0,同時其它不為0值并且大于修改col#的列號col#會相應減1
anbob@PDB1>alter table test drop column id4 ;Table altered.anbob@PDB1>insert into test values(11,12,13,14);insert into test values(11,12,13,14) *ERROR at line 1:ORA-00913: too many valuesanbob@PDB1>insert into test(id,id1,id2,id3) values(11,12,13,14);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>alter table test modify id2 visible;Table altered.anbob@PDB1>alter table test modify id1 visible;Table altered.sys@ORA12C>select name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') from col$ where obj#=91771;NAME COL# INTCOL# SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID 1 1 1 0ID1 4 2 2 0ID2 3 3 3 0ID3 2 4 4 0anbob@PDB1>alter table test modify id1 invisible;Table altered.anbob@PDB1>alter table test add id4 int;Table altered.anbob@PDB1>alter table test modify id1 visible;Table altered.anbob@PDB1>desc test; Name Null? Type ---------------- -------- --------------------------------- ID NUMBER(38) ID3 NUMBER(38) ID2 NUMBER(38) ID4 NUMBER(38) ID1 NUMBER(38)anbob@PDB1>select * from test; ID ID3 ID2 ID4 ID1-------------------- -------------------- -------------------- -------------------- -------------------- 1 4 3 2 11 14 13 12note:注意此時的列順序沒有還原為原來的順序，如果程序使用的select * from 方式而控制列順序顯示，將讓人誤解。如果是insert tablename values() 方式呢? 如果對于一些老程序而使用了insert 未指定列， 而且你使用了此特性做了一個隱藏再顯示的小測試， 這下麻煩可就大了， 對于只隱藏可能會提示你列數不匹配，如果隱藏再顯示，導致列順序錯亂，而有恰巧列類型一致數據庫insert 成功，那數據寫錯了列又無提示，對于客戶數據是災難性的。
anbob@PDB1>alter table test drop column id4 ;Table altered.anbob@PDB1>select * from test; ID ID3 ID2 ID1-------------------- -------------------- -------------------- -------------------- 1 4 3 2 11 14 13 12anbob@PDB1>insert into test(id,id1,id2,id3) values(21,22,23,24);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>select * from test; ID ID3 ID2 ID1-------------------- -------------------- -------------------- -------------------- 1 4 3 2 11 14 13 12 21 24 23 22note:對于程序嚴格指定了列名的就沒有任何影響，下面我們來dump 一下存儲結構，看這些順序會不會影響列的存儲順序。
anbob@PDB1>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,id from test; FILE# BLOCK# ID-------------------- -------------------- -------------------- 10 143 1 10 143 11 10 143 21sys@ORA12C>alter system dump datafile 10 block 143;block_row_dump:tab 0, row 0, @0x1f89tl: 15 fb: --H-FL-- lb: 0x0 cc: 4col 0: [ 2] c1 02col 1: [ 2] c1 03col 2: [ 2] c1 04col 3: [ 2] c1 05tab 0, row 1, @0x1f7atl: 15 fb: --H-FL-- lb: 0x0 cc: 4col 0: [ 2] c1 0ccol 1: [ 2] c1 0dcol 2: [ 2] c1 0ecol 3: [ 2] c1 0ftab 0, row 2, @0x1f6btl: 15 fb: --H-FL-- lb: 0x1 cc: 4col 0: [ 2] c1 16col 1: [ 2] c1 17col 2: [ 2] c1 18col 3: [ 2] c1 19end_of_block_dumpnote:顯然列不是基于此列的順序存儲的，也許是 INTCOL#或者SEGCOL# ,對于列值的還原可以參考http://www.anbob.com/archives/1466.html
Summary:對于新特性要了解其原理后再使用，隱藏列控制了select* 和insert values未指定列名時的顯示，對于隱藏列顯示指定列名還是可以顯示，對于列順序錯亂目前保守的做法是可以rename原表，建原表名view 指定列順序或逐次invisible visible，如果自己測試可以嘗試上面我提到的直接update col$.col#列。
新聞熱點
疑難解答
