Metadata API in Oracle 9i

DBMS_METADATA allows a user to get metadata information about any object from the data dictionary with a single command.

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),
        "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 last published in July 2002

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close