Metadata API in Oracle 9i

It is quite difficult to get metadata out of Oracle 8i and earlier versions. The two most popular methods are:

  1. Querying the data dictionary. In many cases you need more than one select statement to get the required information.
  2. Exporting your schema with ROWS=N and then import with SHOW=Y. This creates a text file, which you then edit to get the metadata. Both methods take a considerable amount of time.

However, in Oracle 9i, a new package was introduced: DBMS_METADATA. It allows users to get metadata information about any object (tables, indexes, procedures, etc) from a data dictionary with a single command.

DBMS_METADATA provides two types of retrieval interfaces:

  1. For browsing: GET_XML and GET_DDL return metadata for a single object.
  2. For programmatic use: 
    3.  OPEN,
 SET_FILTER,
 SET_COUNT,
 GET_QUERY,
 SET_PARSE_ITEM,
 ADD_TRANSFORM,
 SET_TRANSFORM_PARAM,
 FETCH_XXX,
 CLOSE

These allow for flexible selection criteria and extraction of groups of objects.

Example 1 - To get the metadata of Table EMP of SCOTT user, execute the following statement. 

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL( 'TABLE','EMP','SCOTT') FROM DUAL;
In this SQL statement, you can ignore the schema if you are extracting from the current schema. The output would be: 
  CREATE TABLE "SCOTT"."EMP"
   (    "COLUMN1" VARCHAR2(20),
        "COLUMN2" NUMBER(10,2),

    Requires Free Membership to View

    Login 

 "COLUMN3" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"
From the above statement, we can see that it also returns storage clauses. To specify that storage clauses are not to be returned in the SQL DDL, use the following: 
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
  DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
To reset the session-level parameters to the default, use the following command: 
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
   DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
Example 2 - This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. 

set pagesize 0
set long 90000
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');
You can use spool to get all DDL into a file.

Example 3 - Fetch the DDL for all object grants on SCOTT.EMP: 

SET PAGESIZE 0
SET LONG 90000
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMP', 'SCOTT') FROM DUAL;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in July 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top