Tapping into Oracle's Metadata - Part I

James Koopmann's picture
articles: 

In this article James explores the Oracle's Metadata API (DBMS_METADATA) and shows how database users can extract object definitions (DDL statements) from an Oracle database without having to go through a stack of dictionary views.

Having grown up with Oracle since version 6, and I am sure many of you out there are in my same boat, it has become second nature to rattle off the various DBA_ views that make up an objects metadata. A subset being DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_IND_COLUMNS, DBA_CONSTRAINTS, and DBA_CONS_COLUMNS. The ability to rattle of these objects as well as the columns that make up these views stems from the fact that, in the olden days, there were not any modeling tools that we were able to develop structures with, let alone be able to reverse engineer into. Thus we needed to be able to compare database structures attribute by attribute. In order to recreate a database schema or object we needed to devise SQL that would extract some form of DDL or keep tight control through some form of version control for our database structures if we ever wanted to re-create the database from scratch. Yes, export and import were around but very difficult to work with for individual objects at times.

Oracle has what I like to think is a good solution to the issues of DDL extraction, storage, and recreation of objects through their metadata API namely DBMS_METADATA. This mechanism allows us to focus on maintaining our database.

In the simplest form, there are three function calls that allow you to extract DDL or XML on a named object. In the purist form these function calls have the following syntax.

Simple form of DBMS_METADATA.GET functions

FunctionParameters
Get DDL for a single named object.
DBMS_METADATA.GET_DDL

DBMS_METADATA.GET_XML
(Object_type IN VARCHAR2,
Name IN VARCHAR2,
Schema IN VARCHAR2)
Get DDL for dependent objects against the base object supplied.
DBMS_METADATA.GET_DEPENDENT_DDL

DBMS_METADATA.GET_DEPENDENT_XML
(Object_type IN VARCHAR2,
Base_object_name IN VARCHAR2,
Base_object_schema IN VARCHAR2)
Get DDL for grants issued agains an object.
DBMS_METADATA.GET_GRANTED_DDL

DBMS_METADATA.GET_GRANTED_XML
(Object_type IN VARCHAR2,
Grantee IN VARCHAR2,
Schema IN VARCHAR2)

Input Parameters Defined

ParameterDescription
object_type
Is an Oracle "named" object.
Can be :
1. An Object types you can see from querying the DBA_OBJECTS view for object_type.
2. A predefined type supplied by Oracle for use in the DBMS_METADATA functions such as REF_CONSTRAINT, OBJECT_GRANT, etc.
name / base_object_name
For 'name' it is an Oracle named "object" that is defined by the object_type.
For base_object_name it is the object_type that is defined by the named object.

These differences will be explained in the examples.

Schema / base_object_schema
Owner of the object.
grantee
The grantee. It is used internally in a GRANTEE filter. The default is the current user.

DBMS_METADATA.GET_DDL

One of the easiest ways to get a peek into this metadata extraction is to run the following SQL for one of your object types. Just replace the table name and you are good to go. This example shows how the named object (<table_name>) is dependent on the object_type "TABLE". All the uses of the GET_DDL function call fall into this clean relationship where the named object is of type object_type.

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('TABLE','<table_name>') FROM dual;

You can do some very powerful things with just this simple function call. For instance if you wanted to get the DDL for all tables in a particular schema you need only join the function to the DBA_TABLES view such like the following SQL. Just keep in mind that the GET_DDL call takes in other valid objects types such as USER, TRIGGER, SEQUENCE, etc.

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name)
FROM DBA_TABLES WHERE OWNER = '<schema>'

If you wanted to store all table DDL for a particular schema you need only create a table to hold the information and issue some simple SQL.

CREATE TABLE my_ddl (owner VARCHAR2(30), 
                     table_name VARCHAR2(30),
                     ddl   CLOB);

INSERT INTO my_ddl (owner, table_name, ddl)
SELECT owner, table_name,
DBMS_METADATA.GET_DDL('TABLE', table_name, owner) ddl
FROM DBA_TABLES WHERE OWNER = '<schema>';

DBMS_METADATA.GET_DEPENDENT_DDL

Often times we need to determine how a particular object relates to other objects in our database. This is what the GET_DEPENDENT_DDL function was created for. With this function it is easy to determine such things as the grants on an object, the referential integrity between objects, and the indexes on an object. This function can be a bit confusing when supplying inputs to the function call. What is needed to remember is that your are asking for items that are dependent on a named object in Oracle. Here are a few examples to help you get familiar with the proper use of this powerful function.

Suppose you wanted to extract all source code for triggers where a particular table was used in? You no longer need to search through DBA_SOURCE you need only issue the following SQL. Please note that it is the object_type that is dependent on the named object <table_name>. Extend this query a bit and you can see how you might be able to extract all source code for an object with a simple changing of TRIGGER to include PROCEDURE, PACKAGE, FUNCTION, and JAVA_SOURCE.

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','<table_name>','<schema>') from dual;

Now if you wanted to find all indexes on a particular table and generate the DDL you need only change the object_type from TRIGGER to INDEX.

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','<table_name>','<schema>') from dual;

Sometimes when we migrate objects between schemas or databases we often forget about the constraints that need to be put in place. These are sometimes the hardest to extract from a database and get issued. Now we need only supply the pre-defined Oracle type to the GET_DEPENDENT_DDL function and we can see all the DDL for constraints on an object.

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','<table_name>','<schema>') from dual

DBMS_METADATA.GET_GRANTED_DDL

The purpose of the GET_GRANTED_DDL function is to generate the DDL required around grants given to a particular user.

If you wanted to generate the system grants given to a user.

select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;

If you wanted to generate the roles granted to a user.

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;

If you wanted all the object grants given to a user.

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;

The DBMS_METADATA package has unlimited use from the standpoint of just these three functions. Don't lose track of your ability to interject power into these function calls by linking them into the already supplied DBA views. When you do this you can generate DDL that allows you to migrate either whole object definitions or objects through relationships. Also hidden within the function calls are place holders for generating DDL on a given version of Oracle and also a holder for a target database system defined by "model". Currently the version only goes back to version 9.2.0 and the model default is "ORACLE". I can easily see these parameters opening up to other database versions and other databases such as DB2 and SQL Server. The DBMS_METADATA API has many other facets that will I hope become clear in part two of this series on tapping into Oracle's metadata.

Comments

Thanks you for the article. Your article made me use this package rather than using import utility in extracting DDL.

Thanks for the kind words.
Wait till next month and feel the power of this API!
Cheers

Excellent article!! I'm a SQL Server admin, transplanted into an Oracle environment. This info has definitely just given me a leg up - thanks a million!!!

I have just been dumped on a dodgy government DB project from a SYBASE background and been instructed to become an expert in it. I have been trying to figure out how to do this for a while. This little article has probably saved my job, for a while...

Really a smart little article that tells almost everything about DBMS_METADATA usage.

Helped me a lot.

I found this article very helpful and a timesaver. I like the fact that it was concise, yet packed with useful and practical information. Well done.

SQL Express (MSSQL) makes it easy to generate SQL script from an existing database. However, don't know why I cannot find a similar wizard in Oracle's Enterprise Manager!

This article saved me a lot of time!

Hi James,
Your article on dbms_metadata usage was good. I am using your approach to compare the differences between two schemas. When using get_dependent_ddl with 'OBJECT_GRANT', we receive set of grants associated with the base_object_name and base_object_schema. Is there a way to sort the grants before comparing it with another cob ddl?

I know it's lazy, but there's also the free Oracle SQL developer client that will generate DDL on any object. Simply select an object in the left-hand navigator pane, then choose "SQL" from the tabs that come up on the right.