Oracle 数据字典表 -- SYS.COL$
一.?數據字典表SYS.COL$?說明
?
????????????Oracle?的數據字典分兩類，一個數據字典表，另一個是數據字典視圖。
????????????數據字典表里的數據是Oracle系統存放的系統數據，而普通表存放的是用戶的數據。為了方便的區別這些表，這些表的名字都是用"$"結尾，這些表屬于SYS用戶。
????????????數據字典表由$ORACLE_HOME/rdbms/admin/sql.bsq?腳本創建。
?
????????????這些數據字典表名稱不好記，所以Oracle?又根據這些表創建了一些視圖。?即方便使用，又影藏了那些數據字典表。
?
????????????關于數據字典的更多內容，參考我的Blog：
????????????Oracle?數據字典?說明
????????????http://blog.csdn.net/tianlesoftware/archive/2010/09/04/5862508.aspx
?
????????????這里講的SYS.COL$表保存的就是表列的定義信息，但是我們查詢表列的信息時，卻不是直接查詢SYS.COL$，而是查詢USER_TAB_COLUMNS?視圖。
?
SQL> select owner,object_name,object_type from all_objects where object_name='COL$';
?
OWNER??????OBJECT_NAME????????OBJECT_TYPE
----------------- ------------------------------ ------------------
SYS???????????COL$????????????????TABLE
?
?
SQL> set long 9999
SQL> select text from dba_views where view_name ='USER_TAB_COLUMNS';
?
TEXT
--------------------------------------------------------------------------------
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
???????DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
???????DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
???????DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
???????CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
???????GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
???????V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
??from?USER_TAB_COLS
?where HIDDEN_COLUMN = 'NO'
?
這里查看的是USER_TAB_COLS視圖，我們在挖一層：
SQL>select?*?from?dba_views?where?view_name?='USER_TAB_COLS'
select o.name,
???????c.name,
???????decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
???????????????????????2, decode(c.scale, null,
?????????????????????????????????decode(c.precision#, null, 'NUMBER', 'FLOAT'),
?????????????????????????????????'NUMBER'),
???????????????????????8, 'LONG',
???????????????????????9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
???????????????????????12, 'DATE',
???????????????????????23, 'RAW', 24, 'LONG RAW',
???????????????????????58, nvl2(ac.synobj#, (select o.name from obj$ o
????????????????????????????????where o.obj#=ac.synobj#), ot.name),
???????????????????????69, 'ROWID',
???????????????????????96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
???????????????????????100, 'BINARY_FLOAT',
???????????????????????101, 'BINARY_DOUBLE',
???????????????????????105, 'MLSLABEL',
???????????????????????106, 'MLSLABEL',
???????????????????????111, nvl2(ac.synobj#, (select o.name from obj$ o
?????????????????????????????????where o.obj#=ac.synobj#), ot.name),
???????????????????????112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
???????????????????????113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
???????????????????????121, nvl2(ac.synobj#, (select o.name from obj$ o
?????????????????????????????????where o.obj#=ac.synobj#), ot.name),
???????????????????????122, nvl2(ac.synobj#, (select o.name from obj$ o
?????????????????????????????????where o.obj#=ac.synobj#), ot.name),
???????????????????????123, nvl2(ac.synobj#, (select o.name from obj$ o
?????????????????????????????????where o.obj#=ac.synobj#), ot.name),
???????????????????????178, 'TIME(' ||c.scale|| ')',
???????????????????????179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
???????????????????????180, 'TIMESTAMP(' ||c.scale|| ')',
???????????????????????181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
???????????????????????231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
???????????????????????182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
???????????????????????183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
?????????????????????????????c.scale || ')',
???????????????????????208, 'UROWID',
???????????????????????'UNDEFINED'),
???????decode(c.type#, 111, 'REF'),
???????nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o
?????????????????????????where o.owner#=u.user# and o.obj#=ac.synobj#),
????????????ut.name),
???????c.length, c.precision#, c.scale,
???????decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
???????decode(c.col#, 0, to_number(null), c.col#), c.deflength,
???????c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
???????case when nvl(h.distcnt,0) = 0 then h.distcnt
????????????when h.row_cnt = 0 then 1
??????????when (h.bucket_cnt > 255
??????????????????or
??????????????????(h.bucket_cnt > h.distcnt
???????????????????and h.row_cnt = h.distcnt
???????????????????and h.density*h.bucket_cnt < 1))
????????????????then h.row_cnt
????????????else h.bucket_cnt
???????end,
???????h.timestamp#, h.sample_size,
???????decode(c.charsetform, 1, 'CHAR_CS',
?????????????????????????????2, 'NCHAR_CS',
?????????????????????????????3, NLS_CHARSET_NAME(c.charsetid),
?????????????????????????????4, 'ARG:'||c.charsetid),
???????decode(c.charsetid, 0, to_number(NULL),
???????????????????????????nls_charset_decl_len(c.length, c.charsetid)),
???????decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
???????decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
???????h.avgcln,
???????c.spare3,
???????decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
??????????????????????96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
??????????????????????null),
???????decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
???????decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
????????????????????????decode(bitand(ac.flags, 2), 2, 'NO',
???????????????????????????????decode(bitand(ac.flags, 4), 4, 'NO',
??????????????????????????????????????decode(bitand(ac.flags, 8), 8, 'NO',
?????????????????????????????????????????????'N/A')))),
???????decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
??????????????????????????????????????????'NO')),
???????decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
??????????????????????????????????????????'NO')),
???????decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
???????case when nvl(h.row_cnt,0) = 0 then 'NONE'
????????????when (h.bucket_cnt > 255
??????????????????or
??????????????????(h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
???????????????????and h.density*h.bucket_cnt < 1))
????????????????then 'FREQUENCY'
????????????else 'HEIGHT BALANCED'
???????end,
???????decode(bitand(c.property, 1024), 1024,
??????????????(select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
???????????????from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
???????????????and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
???????????????cl.intcol# = rc.intcol#(+)),
??????????????decode(bitand(c.property, 1), 0, c.name,
?????????????????????(select tc.name from sys.attrcol$ tc
??????????????????????where c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h,
?????sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut
where o.obj# = c.obj#
??and bitand(o.flags, 128) = 0
??and o.owner# = userenv('SCHEMAID')
??and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
??and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
??and ac.toid = ot.oid$(+)
??and ot.type#(+) = 13
??and ot.owner# = ut.user#(+)
??and (o.type# in (3, 4)????????????????????????????????????/* cluster, view */
???????or
???????(o.type# = 2????/* tables, excluding iot - overflow and nested tables */
????????and
????????not exists (select null
??????????????????????from sys.tab$ t
?????????????????????where t.obj# = o.obj#
???????????????????????and (bitand(t.property, 512) = 512 or
????????????????????????????bitand(t.property, 8192) = 8192))))
?
在這里，我們看到了本質：sys.col$?。
?
????????????Oracle數據庫沒有提供直接修改表中列名稱的功能，但在實際使用時常需要修改表的列名和列順序。
????????????我們可以通過間接的方法來實現，就是重新創建一個新的具有正確列名和順序的數據庫表，再將舊表的數據轉儲進來，最后刪除舊表并將新表重命名為舊表的方法來完成此功能。
????????????這種方法的最大問題是要求有雙倍的存儲空間、較大的回滾段和較長的時間，如果表中數據量較大，這項工作開銷會很大。
?
????????????而SYS.COL$保存的就是表列的信息，所以我們可以直接修改這個表列的信息，從而改變表中列的順序。?當然Oracle?是不建議這么操作的。?這里也只做個知識點了解一下。
?
?
二.??SYS.COL$?示例
2.1?創建測試表
SQL> conn dave/dave;
已連接。
SQL> create table myuser as select username,user_id from all_users;
表已創建。
SQL> select * from myuser where rownum=1;
USERNAME??????????????????????????USER_ID
------------------------------ ----------
DAVE???????????????????????????????????90
?
下面的操作就是將2個列換一下順序，并將列名改為ID?和?NAME.
?
2. 2?從ALL_OBJECTS中查找對象DAVE.MYUSER表的ID
?在第一節里我們將了，對象的表列信息是存放在SYS.COL$表里的，要修改對象的列，就需要知道對象的ID.
?
SQL> SELECT OBJECT_NAME,OBJECT_ID FROM ALL_OBJECTS WHERE OWNER ='DAVE' AND OBJECT_NAME='MYUSER';
?
OBJECT_NAME???????OBJECT_ID
------------------------------ ----------
MYUSER??????????????74344
?
2.3根據MYUSER的ID，從SYS.COL$檢索出表中列的定義信息
SQL> conn / as sysdba;
已連接。
SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =74344;
?
??????OBJ#???????COL# NAME
---------- ---------- ------------------------------
?????74344??????????1 USERNAME
?????74344??????????2 USER_ID
?
注意：SYS.COL$?只能sys?用戶才有權限查詢。
?
2.4?使用Update語句來進行修改
SQL> UPDATE SYS.COL$ SET COL# = 1,NAME='ID' WHERE OBJ# = 74344 AND NAME='USER_ID';
已更新?1?行。
?
SQL> UPDATE SYS.COL$ SET COL# = 2,NAME='NAME' WHERE OBJ# = 74344 AND NAME ='USERNAME';
已更新?1?行。
?
SQL> COMMIT;
提交完成。
?
2.5?重啟數據庫服務
????????????由于數據字典是在數據庫啟動時加載到SQL中的，所以修改了它之后，還需要重啟數據庫服務。
?
SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE?例程已經關閉。
SQL> startup
ORACLE?例程已經啟動。
?
Total System Global Area 1071333376 bytes
Fixed Size??????????????????1375792 bytes
Variable Size?????????????436208080 bytes
Database Buffers??????????629145600 bytes
Redo Buffers????????????????4603904 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL>
?
2.6?再查看
SQL> conn dave/dave;
已連接。
SQL> select * from myuser where rownum=1;
?
????????ID NAME
---------- ------------------------------
????????90 DAVE
?
修改已經成功。
?
????????????直接修改數據字典表是個危險的操作。?所以以上測試僅做了解。
總結
以上是生活随笔為你收集整理的Oracle 数据字典表 -- SYS.COL$的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 如何通过dblink truncate远
- 下一篇： Oracle ASM 详解