常用数据字典

1.DICT
主要显示当前用户可以访问的所有数据字典视图，并有数据字典的作用描述。
SQL> desc dict               
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TABLE_NAME                                                     VARCHAR2(30)
 COMMENTS                                                       VARCHAR2(4000)

查询和授权有关的数据字典视图
SQL>  select * from dict where COMMENTS like '%grant%';
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------
USER_COL_PRIVS                 Grants on columns for which the user is
                                       the owner, grantor or grantee

2.DICT_COLUMNS
用于显示数据字典每列的作用。
如查询数据字典USER_SEGMENTS每列的作用。
SQL> select * from dict_columns where TABLE_NAME='USER_SEGMENTS';
TABLE_NAME                     COLUMN_NAME     COMMENTS
------------------------------ --------------- ----------------------------------------
USER_SEGMENTS                  SEGMENT_NAME    Name, if any, of the segment
USER_SEGMENTS                  PARTITION_NAME  Partition/Subpartition Name, if any, of
                                               the segment

USER_SEGMENTS                  SEGMENT_TYPE    Type of segment:  "TABLE", "CLUSTER", "I
                                               NDEX", "ROLLBACK", "DEFERRED ROLLBACK",
                                               "TEMPORARY", "SPACE HEADER", "TYPE2 UNDO
                                               " or "CACHE"

3.DUAL
用于取得函数的返回值
SQL> desc dual
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DUMMY                                                          VARCHAR2(1)

SQL> select user from dual;

USER
------------------------------
SYS

4.GLOBAL_NAME
显示当前数据库的全名
SQL> select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

5.IND
显示当前用户拥有的所有索引和索引统计信息。
例如：查询emp表的索引
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,UNIQUENESS from ind where table_name='EMP';

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
PK_EMP                         NORMAL                      SCOTT                          UNIQUE

6.OBJ
用于显示当前用户所拥有的所有对象。
例如查询scott用户所拥有的所有对象（表）；
SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,CREATED from obj
     where object_type='TABLE';

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID CREATED
---------- ---------- -------------- ---------
DEPT            52540          52540 02-MAY-11
EMP             52542          52542 02-MAY-11

7。SEQ
用于显示当前用户所拥有的所有序列。
SQL> show user
USER is "SCOTT"
SQL> conn / as sysdba
Connected.
SQL> select SEQUENCE_NAME,INCREMENT_BY from seq;
\
SEQUENCE_NAME                  INCREMENT_BY
------------------------------   ------------
UGROUP_SEQUENCE                          1
OBJECT_GRANT                               1
SYSTEM_GRANT                                  1

8.TAB
用于显示当前用户所拥有的同义词和同义词所对应的数据库对象名。
SQL> r
  1* select * from syn

SYNONYM_NAME    TABLE_OWNE TABLE_NAME                     DB_LINK
--------------- ---------- ------------------------------ ---------------
DEF$_AQCALL     SYSTEM     DEF$_AQCALL
DEF$_CALLDEST   SYSTEM     DEF$_CALLDEST
DEF$_SCHEDULE   SYSTEM     DEF$_SCHEDULE
DEF$_ERROR      SYSTEM     DEF$_ERROR
DEF$_DEFAULTDES SYSTEM     DEF$_DEFAULTDEST

9.TAB
显示当前用户所拥有的表、视图和序列。
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
OB                             TABLE
SALES                          TABLE
TIMES                          TABLE