Oracle数据字典视图之DICT_COLUMNS（提纲掣领的一篇，该视图是一个工具对深入了解数据字典）

【VIEW】Oracle数据字典视图之DICT_COLUMNS

上一篇 /下一篇  2010-10-13 22:41:31/ 个人分类：Oracle

查看( 489 ) /评论( 0 ) / 评分( 0 / 0 )

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官方文档中的描述
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4195.htm#sthref1972

DICT_COLUMNS

DICT_COLUMNScontains descriptions of columns in data dictionary tables and views.

ColumnDatatypeNULLDescriptionTABLE_NAMEVARCHAR2(30) Name of the object that contains the columnCOLUMN_NAMEVARCHAR2(30) Name of the columnCOMMENTSVARCHAR2(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 --

来自http://space.itpub.net/519536/viewspace-571453