Oracle 数据字典表 -- SYS.COL$

一. 数据字典表SYS.COL$ 说明

 

            Oracle 的数据字典分两类，一个数据字典表，另一个是数据字典视图。

            数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。

            数据字典表由$ORACLE_HOME/rdbms/admin/sql.bsq 脚本创建。

 

            这些数据字典表名称不好记，所以Oracle 又根据这些表创建了一些视图。 即方便使用，又影藏了那些数据字典表。

 

            关于数据字典的更多内容，参考我的Blog：

            Oracle 数据字典 说明

    http://blog.csdn.net/xujinyang/article/details/6831391

 

            这里讲的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

 

修改已经成功。

 

            直接修改数据字典表是个危险的操作。 所以以上测试仅做了解。

 

 

 

 

 

 

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