ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【视图】oracle 数据字典视图之 DICT / DICTIONARY

【视图】oracle 数据字典视图之 DICT / DICTIONARY

原创 Linux操作系统 作者:secooler 时间:2009-03-18 14:59:27 0 删除 编辑
1.当你猛然间忘记通过什么视图可以查看procedure内容时,隐约记得所需的视图包含“SOUR”字段,此时查询dict/dictionary视图是最好的选择
sys@ora10g> col COMMENTS for a78
sys@ora10g> select * from dict where TABLE_NAME like '%SOUR%';

TABLE_NAME                     COMMENTS
------------------------------ -----------------------------------------------------------
USER_RESOURCE_LIMITS           Display resource limit of the user
ALL_SOURCE_TABLES              Source tables available for Change Data Capture
DBA_SOURCE_TABLES              Source tables available for Change Data Capture
USER_SOURCE_TABLES             Source tables available for Change Data Capture
USER_SOURCE                    Source of stored objects accessible to the user
ALL_SOURCE                     Current source on stored objects that user is allowed to create
DBA_SOURCE                     Source of all stored objects in the database
DBA_TSM_SOURCE                 Transparent session migration source session statistics
DBA_HIST_RESOURCE_LIMIT        Resource Limit Historical Statistics Information
DBA_RESOURCE_INCARNATIONS      Resource incarnations that are running or eligible for HA status notification
RESOURCE_COST                  Cost for each resource
V$RESOURCE                     Synonym for V_$RESOURCE
V$RESOURCE_LIMIT               Synonym for V_$RESOURCE_LIMIT
GV$RESOURCE_LIMIT              Synonym for GV_$RESOURCE_LIMIT
GV$RESOURCE                    Synonym for GV_$RESOURCE

15 rows selected.

2.dict/dictionary数据字典视图相关信息
sys@ora10g> desc dict
 Name                                  Null?    Type
 ------------------------------------- -------- ----------------------------
 TABLE_NAME                                     VARCHAR2(30)
 COMMENTS                                       VARCHAR2(4000)

sys@ora10g> desc dictionary;
 Name                                  Null?    Type
 ------------------------------------- -------- ----------------------------
 TABLE_NAME                                      VARCHAR2(30)
 COMMENTS                                        VARCHAR2(4000)

sys@ora10g> select owner, object_name,object_type from dba_objects where object_name in ('DICT','DICTIONARY');

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS                            DICTIONARY                     VIEW
PUBLIC                         DICT                           SYNONYM
PUBLIC                         DICTIONARY                     SYNONYM

3.通过查看catalog.sql可以获得oracle创建dict/dictionary数据字典视图的语句
ora10g@linux5 /oracle/u01/app/oracle/product/10.2.0/db_1/rdbms/admin$ vi catalog.sql
... 忽略无关内容 ...
remark
remark  VIEW "DICTIONARY"
remark  Online documentation for data dictionary tables and views.
remark  This view exists outside of the family schema.
remark
/* Find the names of public synonyms for views owned by SYS that
have names different from the synonym name.  This allows the user
to see the short-hand synonyms we have created.
*/
create or replace view DICTIONARY
    (TABLE_NAME, COMMENTS)
as
select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
  and c.col# is null
  and 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 */)
           )
      )
union all
select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
  and o.owner# = 0
  and o.name in ('AUDIT_ACTIONS', 'COLUMN_PRIVILEGES', 'DICTIONARY',
        'DICT_COLUMNS', 'DUAL', 'GLOBAL_NAME', 'INDEX_HISTOGRAM',
        'INDEX_STATS', 'RESOURCE_COST', 'ROLE_ROLE_PRIVS', 'ROLE_SYS_PRIVS',
        'ROLE_TAB_PRIVS', 'SESSION_PRIVS', 'SESSION_ROLES',
        'TABLE_PRIVILEGES','NLS_SESSION_PARAMETERS','NLS_INSTANCE_PARAMETERS',
        'NLS_DATABASE_PARAMETERS', 'DATABASE_COMPATIBLE_LEVEL',
        'DBMS_ALERT_INFO', 'DBMS_LOCK_ALLOCATED')
  and c.col# is null
union all
select so.name, 'Synonym for ' || sy.name
from sys.obj$ ro, sys.syn$ sy, sys.obj$ so
where so.type# = 5
  and ro.linkname is null
  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.owner# = userenv('SCHEMAID')
       or ro.obj# in
           (select oa.obj#
            from sys.objauth$ oa
            where grantee# in (select kzsrorol from x$kzsro))
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  ))
/
comment on table DICTIONARY is
'Description of data dictionary tables and views'
/
comment on column DICTIONARY.TABLE_NAME is
'Name of the object'
/
comment on column DICTIONARY.COMMENTS is
'Text comment on the object'
/

create or replace public synonym DICTIONARY for DICTIONARY
/
create or replace public synonym DICT for DICTIONARY
/
grant select on DICTIONARY to PUBLIC with grant option
/
... 忽略无关内容 ...

4.oracle官方文档中关于该视图的描述

DICTIONARY

DICTIONARY contains descriptions of data dictionary tables and views.

Column Datatype NULL Description
TABLE_NAME VARCHAR2(30)
Name of the object
COMMENTS VARCHAR2(4000)
Text comment on the object

5.小结
dict/dictionary数据字典是一个非常好的自我提醒的工具,当无助的时候一定要想起“她”。

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-571425/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8023882