oracle的col表,Oracle 數據字典表 -- SYS.COL$

一.數據字典表SYS.COL$說明

Oracle的數據字典分兩類，一個數據字典表，另一個是數據字典視圖。

數據字典表里的數據是Oracle系統存放的系統數據，而普通表存放的是用戶的數據。為了方便的區別這些表，這些表的名字都是用"$"結尾，這些表屬於SYS用戶。

數據字典表由$ORACLE_HOME/rdbms/admin/sql.bsq腳本創建。

這些數據字典表名稱不好記，所以Oracle又根據這些表創建了一些視圖。 即方便使用，又影藏了那些數據字典表。

關於數據字典的更多內容，參考我的Blog：

Oracle數據字典說明

這里講的SYS.COL$表保存的就是表列的定義信息，但是我們查詢表列的信息時，卻不是直接查詢SYS.COL$，而是查詢USER_TAB_COLUMNS視圖。

SQL> select owner,object_name,object_type from all_objects where object_name='COL$';

OWNEROBJECT_NAMEOBJECT_TYPE

----------------- ------------------------------ ------------------

SYSCOL$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*fromdba_viewswhereview_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;

USERNAMEUSER_ID

------------------------------ ----------

DAVE90

下面的操作就是將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_NAMEOBJECT_ID

------------------------------ ----------

MYUSER74344

2.3根據MYUSER的ID，從SYS.COL$檢索出表中列的定義信息

SQL> conn / as sysdba;

已連接。

SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =74344;

OBJ#COL# NAME

---------- ---------- ------------------------------

743441 USERNAME

743442 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 Size1375792 bytes

Variable Size436208080 bytes

Database Buffers629145600 bytes

Redo Buffers4603904 bytes

數據庫裝載完畢。

數據庫已經打開。

SQL>

2.6再查看

SQL> conn dave/dave;

已連接。

SQL> select * from myuser where rownum=1;

ID NAME

---------- ------------------------------

90 DAVE

修改已經成功。

直接修改數據字典表是個危險的操作。 所以以上測試僅做了解。

------------------------------------------------------------------------------

Blog：http://blog.csdn.net/tianlesoftware

網上資源：http://tianlesoftware.download.csdn.net

相關視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1群：62697716(滿); DBA2群：62697977(滿)

DBA3群：62697850DBA超級群：63306533;

聊天 群：40132017

--加群需要在備注說明Oracle表空間和數據文件的關系，否則拒絕申請