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 --

【檢視】oracle 資料字典檢視之 DICT / DICTIONARY

DICTIONARY

相關文章