DBA_TAB_COLUMNS和DBA_TAB_COLS 兩個檢視的區別

kewin發表於2011-09-22

Kevin Zou
2011-9-20
dba_tab_columns和dba_tab_cols 是兩個很相似的檢視，都是顯示TABLE的欄位，有啥區別呢？
先看下定義：
SQL> select * from v$version where rownum < 2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
 
SQL>select text from dba_views where view_name='DBA_TAB_COLS'

TEXT
--------------------------------------------------------------------------------

select u.name, 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 ZO

NE',
                       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 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*2 <= 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*2 <= 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.obj$ o, sys.hist_head$ h, sys.user$ u,
     sys.coltype$ ac, sys.obj$ ot, sys.user$ ut
where o.obj# = c.obj#
  and o.owner# = u.user#
  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))))

SQL> select text from dba_views where view_name='DBA_TAB_COLUMNS';

TEXT
--------------------------------------------------------------------------------

select OWNER, 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 DBA_TAB_COLS
 where HIDDEN_COLUMN = 'NO'

從定義上看到DBA_TAB_COLUMNS 是DBA_TAB_COLS的子集，只顯示非隱藏的欄位。
還是通過例子來說：
 
SQL> create table kk (id int, name varchar2(20), age int);

表已建立。
SQL>  select column_name  from dba_tab_columns where table_name='KK' and wner='KK';

COLUMN_NAME
------------------------------
ID
NAME
AGE

SQL>   select column_name,HIDDEN_COLUMN from dba_tab_cols where table_name='KK' and wner='KK';

COLUMN_NAME                    HID
------------------------------ ---
ID                             NO
NAME                           NO
AGE                            NO

SQL>  select column_name  from dba_tab_columns where table_name='KK' and wner='KK';

COLUMN_NAME
------------------------------
ID
AGE
SQL>   select column_name,HIDDEN_COLUMN from dba_tab_cols where table_name='KK'
and wner='KK';

COLUMN_NAME                    HID
------------------------------ ---
ID                             NO
SYS_C00002_11092116:27:09$     YES
AGE                            NO

在設定了UNUSED 的欄位後，NAME 欄位在dba_tab_columns 檢視查詢不到了，在dba_tab_cols檢視中，已經改名為SYS_C00002_11092116:27:09$ 。
還可以通過檢視DBA_UNUSED_COL_TABS來檢視現有系統UNUSED COLUMN：
SQL> SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
KK                             KK                                      1

現在系統只有一個TABLE被設定了UNUSED COLUMN。
被設定了UNUSED COLUMN後，不能被查詢到，不能恢復，只能被DROP。
SQL> alter table kk drop unused columns;

表已更改。
SQL>   select column_name,HIDDEN_COLUMN from dba_tab_cols where table_name='KK'
and wner='KK';

COLUMN_NAME                    HID
------------------------------ ---
ID                             NO
AGE                            NO

這個特性有啥好處呢？
如果要DROP一個大表的COLUMN，將是很耗時的，而且很影響效能。可以先把這個COLUMN標識為UNUSED，然後在DROP以提高處理速度。
例子：


FLASHBACK包括flashback query 不能應用在UNUSED的欄位上:
通過SYS檢視系統當前的SCN:
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2169005

通過使用者KK修改TABLE kk：
SQL> alter table kk set unused column name;

表已更改。

SQL> select * from kk;

        ID        AGE
---------- ----------
         1         30
         1        100
SQL> select * from kk as of  scn  2169005;
select * from kk as of  scn  2169005
              *
第 1 行出現錯誤:
ORA-01466: 無法讀取資料 - 表定義已更改
SQL> flashback table kk to scn 2169005;
flashback table kk to scn 2169005
                *
第 1 行出現錯誤:
ORA-08189: 因為未啟用行移動功能, 不能閃回表
SQL> alter table kk enable row movement;

表已更改。

SQL> flashback table kk to scn 2169005;
flashback table kk to scn 2169005
                *
第 1 行出現錯誤:
ORA-01466: 無法讀取資料 - 表定義已更改

SYS 使用者的物件不能做UNUSED ：
SQL> alter table kk set unused column name;
alter table kk set unused column name
*
第 1 行出現錯誤:
ORA-12988: 無法刪除屬於 SYS 的表中的列

-THE END-


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

相關文章