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