Displaying Information About Schema Objects

Oracle Database provides a PL/SQL package that enables you to determine the DDL that created an object and data dictionary views that you can use to display information about schema objects. Packages and views that are unique to specific types of schema objects are described in the associated chapters. This section describes views and packages that are generic in nature and apply to multiple schema objects.

Using a PL/SQL Package to Display Information About Schema Objects

The Oracle-supplied PL/SQL package procedure DBMS_METADATA.GET_DDL lets you obtain metadata (in the form of DDL used to create the object) about a schema object.

See Also:

Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_METADATA package

Example: Using the DBMS_METADATA Package

The DBMS_METADATA package is a powerful tool for obtaining the complete definition of a schema object. It enables you to obtain all of the attributes of an object in one pass. The object is described as DDL that can be used to (re)create it.

In the following statements the GET_DDL function is used to fetch the DDL for all tables in the current schema, filtering out nested tables and overflow segments. The SET_TRANSFORM_PARAM (with the handle value equal to DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") is used to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the session-level transform parameters are reset to their defaults. Once set, transform parameter values remain in effect until specifically reset to their defaults.

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(
     DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(
     DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

The output from DBMS_METADATA.GET_DDL is a LONG data type. When using SQL*Plus, your output may be truncated by default. Issue the following SQL*Plus command before issuing the DBMS_METADATA.GET_DDL statement to ensure that your output is not truncated:

SQL> SET LONG 9999

Schema Objects Data Dictionary Views

These views display general information about schema objects:

View Description

View	Description
`DBA_OBJECTS` `ALL_OBJECTS` `USER_OBJECTS`	`DBA` view describes all schema objects in the database. `ALL` view describes objects accessible to current user. `USER` view describes objects owned by the current user.
`DBA_CATALOG` `ALL_CATALOG` `USER_CATALOG`	List the name, type, and owner (`USER` view does not display owner) for all tables, views, synonyms, and sequences in the database.
`DBA_DEPENDENCIES` `ALL_DEPENDENCIES` `USER_DEPENDENCIES`	List all dependencies between procedures, packages, functions, package bodies, and triggers, including dependencies on views without any database links.

DBA_OBJECTS

ALL_OBJECTS

USER_OBJECTS

DBA view describes all schema objects in the database. ALL view describes objects accessible to current user. USER view describes objects owned by the current user.

DBA_CATALOG

ALL_CATALOG

USER_CATALOG

List the name, type, and owner (USER view does not display owner) for all tables, views, synonyms, and sequences in the database.

DBA_DEPENDENCIES

ALL_DEPENDENCIES

USER_DEPENDENCIES

List all dependencies between procedures, packages, functions, package bodies, and triggers, including dependencies on views without any database links.

Example 1: Displaying 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 following is the query output:

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

Example 2: Displaying 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 ALL_DEPENDENCIES, USER_DEPENDENCIES, and DBA_DEPENDENCIES data dictionary views can be used to reveal the dependencies for a view. The ALL_SYNONYMS, USER_SYNONYMS, and DBA_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 user jward:

SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME
    FROM DBA_SYNONYMS
    WHERE OWNER = 'JWARD';

The following is the query output:

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