Overview Of Oracle Package DBMS METADATA

In this article, we will look into one of the Oracle packages, DBMS_METADATA, used to retrieve metadata from the database dictionary as XML or creation DDL. In SQL Server, we can use SMOs to retrieve metadata like DDL statements of any object, like table, SP or views etc. In Oracle, we can use DBMS_METADATA package to achieve the same.

This package has multiple functions to retrieve metadata and to submit XML. We are going to focus function Get_XXX(), which will fetch metadata of an object in XML or DDL format, in a single call.

DBMS_METADATA.GET_DDL is used to get metadata of a single object, like a table or SP.

Let’s open SQL*Plus or SQL Developer and fire the below-mentioned query to get the table definition.

First parameter is the object type, like table, view, procedure, or package. Next parameter is object name and third parameter will be schema name.

We can retrieve DDL statements of all user tables in SYSTEM schema, using the following query.

We can use GET_DEPENDENT_DDL to retrieve metadata of dependent objects, like indexes, constraints, or triggers created on a specific object.

Here, we retrieved dependent indexes created on EMPLOYEE table.

GET_GRANTED_DDL is used to retrieve the metadata for granted objects.

Here, we retrieved all system grants to the SYSTEM account

We can use GET_XML to retrieve the metadata in XML format, as shown below.

We can use SET_TRANSFORM_PARAM to set parameters to control STORAGE, TABLESPACE and CONSTRAINTS etc in generated DDL statements by Get_XXX functions.

Here, we set transform parameters to exclude Storage and Tablespace information in generated DDL statement.

We can use the below function, which is a wrapper on DBMS_METADATA.GET_DDL to get DDL statement of an object.

For more details on other available functions of this package, please refer here.

I am ending the things here. I hope this article will be helpful for all.