Oracle数据字典查询技巧

       转自他人笔记,写的很好!分享给大家。
       DICT & DICT_COLUMNS
       ORACLE数据库中有数百个数据字典, 如果不能准确的记住数据字典的名字怎么办? 可以通过查询 DICT 来获得相关数据字典的描述信息:

例如:

SQL> desc dict;

Name                    Null?    Type


----------------------- -------- ----------------


TABLE_NAME                       VARCHAR2(30)


COMMENTS                         VARCHAR2(4000)

SQL> SELECT table_name,SUBSTR(comments,1,80) comments FROM dict WHERE table_name like 'DICT%';

TABLE_NAME                     COMMENTS


------------------------------ --------------------------------------------------------------------------------


DICTIONARY                     Description of data dictionary tables and views


DICT_COLUMNS                   Description of columns in data dictionary tables and views


DICT                           Synonym for


SQL> DESC dict_columns

Name                                                                    Null?    Type


----------------------------------------------------------------------- -------- ------------------------------------------------


TABLE_NAME                                                                       VARCHAR2(30)


COLUMN_NAME                                                                      VARCHAR2(30)


COMMENTS                                                                         VARCHAR2(4000)

SQL> SELECT table_name,column_name,substr(comments,1,50) comments FROM dict_columns WHERE table_name = 'DICTIONARY';

TABLE_NAME                     COLUMN_NAME                    COMMENTS


------------------------------ ------------------------------ --------------------------------------------------


DICTIONARY                     TABLE_NAME                     Name of the object


DICTIONARY                     COMMENTS                       Text comment on

       记住这两张数据字典表: DICT(DICTIONARY) 和DICT_COLUMNS , 就不怕会忘记其他数据字典的表名了. 这也算是一个所谓使用关系模型来管理关系数据库的范例吧.

补充:

DICTIONARY中可以查到所有的数据字典视图。
DICTIONARY的定义来自以下三个表:
sys.obj$,
sys.com$,
sys.syn$

以V$开头的VIEW是动态性能视图,可以从V$FIXED_VIEW_DEFINITION中查到。

posted @ 2009-03-10 10:58  艾阳君  阅读(135)  评论(0编辑  收藏  举报