Listing Information about Schema Objects

• ALL_OBJECTS, USER_OBJECTS

• ALL_CATALOG, USER_CATALOG

• ALL_TABLES, USER_TABLES

• ALL_TAB_COLUMNS, USER_TAB_COLUMNS

• ALL_TAB_COMMENTS, USER_TAB_COMMENTS

• ALL_COL_COMMENTS, USER_COL_COMMENTS

• ALL VIEWS, USER_VIEWS

• ALL_INDEXES, USER_INDEXES

• ALL_IND_COLUMNS, USER_IND_COLUMNS

• USER_CLUSTERS

• USER_CLU_COLUMNS

• ALL_SEQUENCES, USER_SEQUENCES

• ALL_SYNONYMS, USER_SYNONYMS

• ALL_DEPENDENCIES, USER_DEPENDENCIES

Example 1 Listing Different Schema Objects by Type

The following query lists all of the objects owned by the user issuing the query:

SELECT object_name, object_type FROM user_objects;

The query above might return results similar to the following:

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
EMP_DEPT                  CLUSTER
EMP                       TABLE
DEPT                      TABLE
EMP_DEPT_INDEX            INDEX
PUBLIC_EMP                SYNONYM
EMP_MGR                   VIEW

Example 2 Listing Column Information

Column information, such as name, datatype, length, precision, scale, and default data values, can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the EMP and DEPT tables:

SELECT table_name, column_name, data_default
    FROM user_tab_columns
    WHERE table_name = 'DEPT' OR table_name = 'EMP';

Considering the example statements at the beginning of this section, a display similar to the one below is displayed:

TABLE_NAME  COLUMN_NAME     DATA_DEFAULT
----------  --------------- --------------------
DEPT        DEPTNO
DEPT        DNAME
DEPT        LOC             ('NEW YORK')
EMP         EMPNO
EMP         ENAME
EMP         JOB
EMP         MGR
EMP         HIREDATE        (sysdate)
EMP         SAL
EMP         COMM
EMP         DEPTNO

Notice that not all columns have a user-specified default. These columns assume NULL when rows that do not specify values for these columns are inserted.

Example 3 Listing Dependencies of Views and Synonyms

When you create a view or a synonym, the view or synonym is based on its underlying base object. The _DEPENDENCIES data dictionary views can be used to reveal the dependencies for a view and the _SYNONYMS data dictionary views can be used to list the base object of a synonym. For example, the following query lists the base objects for the synonyms created by the user JWARD:

SELECT table_owner, table_name
   FROM all_synonyms
   WHERE owner = 'JWARD';

This query might return information similar to the following:

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------
SCOTT                          DEPT
SCOTT                          EMP