Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Metadata API in Oracle9i
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

Metadata API in Oracle9i

by Daniel T. Liu


Prior to Oracle9i, there are two popular methods to extract metadata from data dictionary. The first method involves querying the data dictionary using SQL statements. The second method involves running Export with ROWS=N, then running Import with SHOW=Y. Oracle9i introduces a new Metadata API (DBMS_METADATA package). It allows database users to easily retrieve complete database object definitions (metadata) from the data dictionary.

Old Methods of Extracting Metadata

Extracting Metadata by Querying the Data Dictionary Directly

In Oracle8i and earlier versions, one way to extract metadata is to query the data dictionary using SQL or writing some PL/SQL packages. This method allows you to extract one single type of database object at a time. The example given below shows how to extract a table definition. You need a different set of SQL statements to extract a complete table definition.

This query will extract table column definition from the sys.dba_tab_columns table. The output of this query gives us the following:

This query will provide the table storage information from sys.dba_tables:

The output of this query gives us the following:

You cannot use the output of these two queries directly as Data Definition Language (DDL). You must either format the results manually or write a more convoluted PL/SQL package or script. In addition, the maintenance on maintaining those metadata is high. Each database object type requires a different set of scripts.

Extracting Metadata using Export and Import

The second method of extracting DDL is to run Export with ROWS=N and then run Import with SHOW=Y. Here are the example scripts.

The output file scott.ddl provides complete DDL. However, the file is not very readable. It still requires substantial editing.

New Method of Extracting Metadata

Metadata API Overview

The Oracle9i Metadata API (DBMS_METADATA PL/SQL package) provides an easy and flexible way to extract definitions of all supported database objects from the data dictionary. The DBMS_METADATA package allows you to retrieve metadata for tables, indexes, triggers, procedures, views, synonyms, grants, and more. In addition, the Metadata API can extract complete definitions of database objects in either creation DDL or XML format.

In general, database users can see the metadata of only their own objects. The SYS user and users with SELECT_CATALOG_ROLE can see the metadata of all objects. It is important to remember that, if a non-privileged user is granted some form of access to an object in another user’s schema, this user can retrieve the grant specification through the Metadata API; however, it is not the objects’ actual metadata.

There are two interfaces for the DBMS_METADATA package:

      • Programmatic Interface
      • Browsing Interface

The programmatic interface provides a detailed and powerful way to extract DDL within your code. The browsing interface provides a very convenient way for SQLPLUS users to extract DDL from the database without writing complicated queries.

Here is the list of the DBMS_METADATA procedures for programmatic interface:

      1. ): specifies type of object to be retrieved
      2. dbms_metadata.set_filter( ): specifies restrictions on objects to be retrieved
      3. dbms_metadata.set_count( ): specifies number of objects to be retrieved n a single FETCH_xxx call.
      4. dbms_metadata.get_query( ): returns text of query used by FETCH_xxx
      5. dbms_metadata.set_parse_item( ): enables output parsing and specifies an object attribute to be parsed and returned
      6. dbms_metadata.add_transform( ): specifies a transform that FETCH_xxx applies to the XML representation of retrieved objects
      7. dbms_metadata.set_transform_param( ): specifies parameters to the XSLT stylesheet identified by the transform_handle returned from the ADD_TRANSFORM procedure
      8. dbms_metadata.fetch_ddl( ) and dbms_metadata.fetch_xml( ): return metadata for objects meeting the criteria
      9. dbms_metadata.close( ): invalidates the handle returned by the OPEN procedure and cleans up the associated state

The book Oracle9i Supplied PL/SQL Packages and Types Reference provides many details on how to use these procedures. You can also find examples (see mddemo.sql and mddemo2.sql) under $ORACLE_HOME/rdbms/demo directory.

The DBMS_METADATA browsing interface provides the following procedures:

      1. dbms_medatata.get_ddl( ) and dbms_metadata.get_xml( ): provide a way to return metadata for a single object in DDL or XML format
      2. dbms_metadata.get_dependent_ddl( ) and dbms_metadata.get_dependent_xml( ): return the metadata for one or more dependent objects in DDL or XML format
      3. dbms_metadata.get_granted_ddl( ) and dbms_metadata.get_granted_xml( ): return the metadata for one or more granted objects in DDL or XML format

In this article, I will provide some examples of using browsing interface; specifically, using the GET_DLL function to get DDL from the database. The GET_DDL function returns the metadata for a single object as DDL.

The dbms_metadata.get_ddl function returns one object at a time, but there may be times when you want to extract multiple objects’ DDL from the database. You may also want to extract metadata by database object type.

This example script extracts all tables’ DDL from Scott schema.

The first part of the script generates a spool file called generate_table_ddl.sql:

$ cat generate_table_ddl.sql

The second part of the script runs the generate_table_ddl.sql, and spool out table metadata in table_ddl.sql file:

And this is a slight modification on the SELECT statement in the script file.

We can also create a script to extract all the index definitions from Scott’s schema. Before Oracle9i, the extracting procedure syntax is never easy. This script shows how to use dbms_metadata.get_ddl() to extract stored procedure DDL from user SCOTT.

The first part of the script generates a spool file called generate_proc_ddl.sql:

   $ cat generate_table_ddl.sql

If you run generate_proc_ddl.sql, it will generate formatted procedure syntax.


The Oracle9i Metadata API provides a new and easy way of extracting complete object definition for version control, reverse engineering, object re-creation, and transformation of the database objects.


Oracle Metalink Support (requires login and registration)

Oracle9i Database Utilities

Oracle9i Supplied PL/SQL Packages and Types Reference

Top DBA Shell Scripts for Monitoring Database,” Daniel T. Liu;

I would also like to acknowledge the assistance of Husam Tomeh of FARES.


Daniel Liu is a senior Oracle Database Administrator at First American Real Estate Solutions in Anaheim, CA, and co-author of Oracle Database 10g New Features. His expertise includes Oracle database administration, performance tuning, Oracle networking, and Oracle Application Server. As an Oracle Certified Professional, he taught Oracle certified DBA classes and IOUG University Seminar. Daniel has published articles with DBAzine, Oracle Internals, and SELECT Journal. Daniel holds a Master of Science degree in computer science from Northern Illinois University.

All companies and product names are trademarks or registered trademarks of the respective owners. Please report errors in this article to the author. Neither FARES nor the author warrants that this document is error-free.

Contributors : Daniel T. Liu, Husam Tomeh
Last modified 2005-02-24 01:34 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone