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 first published in July 2002

Dig deeper on Oracle MDM and metadata

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close