DICT_COLUMNS檢視記錄著資料字典表欄位的資訊，是DICT檢視的有效補充。該檢視是自我學習和提示的有效手段。

1.DICT_COLUMNS檢視作用
1）透過COMMENTS欄位可獲得欄位的描述資訊，有助於欄位的深入理解
sys@ora10g> select * from dict_columns where TABLE_NAME='DICT';

TABLE_NAME        COLUMN_NAME                    COMMENTS
----------------- ------------------------------ --------------------
DICT              TABLE_NAME                     Name of the object
DICT              COMMENTS                       Text comment on the object

2）查詢都有哪些表包含指定的欄位
sys@ora10g> select * from dict_columns where COLUMN_NAME='TABLE_NAME' and rownum<4;

TABLE_NAME        COLUMN_NAME                    COMMENTS
----------------- ------------------------------ ---------------------
USER_CATALOG      TABLE_NAME                     Name of the object
ALL_CATALOG       TABLE_NAME                     Name of the object
DBA_CATALOG       TABLE_NAME                     Name of the object

3）查詢哪些表具有超級多的欄位
sys@ora10g> SELECT *
FROM (SELECT   table_name, COUNT (*)
            FROM dict_columns
        GROUP BY table_name
        ORDER BY 2 DESC)
WHERE ROWNUM < 10;

TABLE_NAME                       COUNT(*)
------------------------------ ----------
GV$SESSION                             81
V$SESSION                              80
GV$SQL                                 72
V$SQL                                  71
GV$SQL_PLAN_STATISTICS_ALL             69
GV$SQLAREA                             68
V$SQL_PLAN_STATISTICS_ALL              68
GV$DISPATCHER_RATE                     67
V$SQLAREA                              67

9 rows selected.

從上面的查詢中可以看出GV$SESSION檢視包含的欄位最多，共包含81列！
利用這個方法也可以對具體專案中資料庫設計進行評估，對包含過多列的表進行重點的評審，防止設計缺陷。

2.檢視DICT_COLUMNS的出處——catalog.sql
我們可以在catalog.sql指令碼中找到DICT_COLUMNS的建立語句，這些內容是理解DICT_COLUMNS的實現非常有幫助。
create or replace view DICT_COLUMNS
    (TABLE_NAME, COLUMN_NAME, COMMENTS)
as
select o.name, c.name, co.comment$
from sys.com$ co, sys.col$ c, sys.obj$ o
where o.owner# = 0
and o.type# = 4
and (o.name like 'USER%'
       or o.name like 'ALL%'
       or (o.name like 'DBA%'
           and exists
                   (select null
                    from sys.v$enabledprivs
                    where priv_number = -47 /* SELECT ANY TABLE */)
           )
      )
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.col# = co.col#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
union all
select o.name, c.name, co.comment$
from sys.com$ co, sys.col$ c, sys.obj$ o
where o.owner# = 0
and o.name in ('AUDIT_ACTIONS','DUAL','DICTIONARY', 'DICT_COLUMNS')
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.col# = co.col#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
union all
select so.name, c.name, co.comment$
from sys.com$ co,sys.col$ c, sys.obj$ ro, sys.syn$ sy, sys.obj$ so
where so.type# = 5
and so.owner# = 1
and so.obj# = sy.obj#
and so.name <> sy.name
and sy.owner = 'SYS'
and sy.name = ro.name
and ro.owner# = 0
and ro.type# = 4
and ro.obj# = c.obj#
and c.col# = co.col#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
and c.obj# = co.obj#(+)
/
comment on table DICT_COLUMNS is
'Description of columns in data dictionary tables and views'
/
comment on column DICT_COLUMNS.TABLE_NAME is
'Name of the object that contains the column'
/
comment on column DICT_COLUMNS.COLUMN_NAME is
'Name of the column'
/
comment on column DICT_COLUMNS.COMMENTS is
'Text comment on the object'
/
create or replace public synonym DICT_COLUMNS for DICT_COLUMNS
/
grant select on DICT_COLUMNS to PUBLIC with grant option
/

3.oracle官方文件中的描述
參考連結：

DICT_COLUMNS

DICT_COLUMNS contains descriptions of columns in data dictionary tables and views.

Column	Datatype	Description
TABLE_NAME	VARCHAR2(30)	Name of the object that contains the column
COLUMN_NAME	VARCHAR2(30)	Name of the column
COMMENTS	VARCHAR2(4000)	Text comment on the column

4.與表欄位描述相關的其他檢視
根據DICT_COLUMNS檢視的功能，我們可以在系統中透過DICT檢視獲得與表欄位有關的檢視。
sys@ora10g> select table_name from dict where table_name like '%COLUMNS%' order by 1;

TABLE_NAME
------------------------------
ALL_APPLY_CONFLICT_COLUMNS
ALL_APPLY_KEY_COLUMNS
ALL_APPLY_TABLE_COLUMNS
ALL_AUDIT_POLICY_COLUMNS
ALL_CONS_COLUMNS
ALL_CONS_OBJ_COLUMNS
ALL_ENCRYPTED_COLUMNS
ALL_IND_COLUMNS
ALL_JOIN_IND_COLUMNS
ALL_LOG_GROUP_COLUMNS
ALL_PART_KEY_COLUMNS
ALL_PUBLISHED_COLUMNS
ALL_REPFLAVOR_COLUMNS
ALL_REPKEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS
ALL_SUBSCRIBED_COLUMNS
ALL_TAB_COLUMNS
ALL_UPDATABLE_COLUMNS
DBA_APPLY_CONFLICT_COLUMNS
DBA_APPLY_KEY_COLUMNS
DBA_APPLY_TABLE_COLUMNS
DBA_AUDIT_POLICY_COLUMNS
DBA_CLU_COLUMNS
DBA_CONS_COLUMNS
DBA_CONS_OBJ_COLUMNS
DBA_ENCRYPTED_COLUMNS
DBA_IND_COLUMNS
DBA_JOIN_IND_COLUMNS
DBA_LOG_GROUP_COLUMNS
DBA_OLDIMAGE_COLUMNS
DBA_PART_KEY_COLUMNS
DBA_PUBLISHED_COLUMNS
DBA_REPFLAVOR_COLUMNS
DBA_REPKEY_COLUMNS
DBA_SUBPART_KEY_COLUMNS
DBA_SUBSCRIBED_COLUMNS
DBA_TAB_COLUMNS
DBA_UPDATABLE_COLUMNS
DICT_COLUMNS
USER_AUDIT_POLICY_COLUMNS
USER_CLU_COLUMNS
USER_CONS_COLUMNS
USER_CONS_OBJ_COLUMNS
USER_ENCRYPTED_COLUMNS
USER_IND_COLUMNS
USER_JOIN_IND_COLUMNS
USER_LOG_GROUP_COLUMNS
USER_OLDIMAGE_COLUMNS
USER_PART_KEY_COLUMNS
USER_PUBLISHED_COLUMNS
USER_REPFLAVOR_COLUMNS
USER_REPKEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
USER_SUBSCRIBED_COLUMNS
USER_TAB_COLUMNS
USER_UPDATABLE_COLUMNS

56 rows selected.

5.小結
Oracle提供了很多用於自我理解和學習的途徑。很多檢視本身就是工具。

Good luck.

secooler
10.10.13

-- The End --

【VIEW】Oracle資料字典檢視之DICT_COLUMNS

DICT_COLUMNS

相關文章