As a DBA, I find myself frequently in the situation that it would be nice to have the DDL of an object. One of the most common cases is if a database object got invalid. This invalidation could be during an application deployment or a database migration (new database, new version). It is useful to know the definition of the invalid object to find the root cause of the invalidation.

There are different ways of getting the definition of an object out of the database. Some of them will be discussed in future blog posts (data pump, sqlCL). The way I describe in this blog post is the usage of the DBMS_METADATA package. As a DBA I use this package if I’m working directly on a server and I am connected to the database via sqlplus. It is an easy way of analyzing the structure of an object.

What is DBMS_METADATA capable of?

The package is compelling to describe all the functionalities the package provides this article would be too large. With this package, you are able to gather object metadata for objects in XML, SCML or DDL format and you are also able to work with XML which would be sent to the database.

In this article, I only focus on getting DDL statements for an object.

If you are logged as user SYS or as a user with the SELECT_CATALOG role you can gather the DDL for all database objects. If you are logged in with a different user, you are only able to see the metadata of your objects.


The subprogram for extracting DDL from any database object is GET_DDL. This subprogram requires two parameters OBJECT_TYPE and NAME. If you do not own the database object, you need the SCHEMA parameter additionally.

You can use DBMS_METADATA.GET_DDL directly in a SQL statement. The easiest way is in combination with the dual table. Be aware if you get the metadata of a table you also get the default storage options for your current installation:

If you would like to suppress the storage, you can set the TRANSFORM Parameters for this execution. There are many parameters you can set for the TRANSFORM of the output ( TRANSFORM documentation). To disable all storage parameters you can use (SEGMENT_ATTRIBUTES). There is also a parameter PRETTY (Default => TRUE) which formats the output with line intents and feeds.


If you need to extract the DDL of some statements to either compare the DDL with the one you have in your source control or to find bugs in your application, DBMS_METADATA is an easy tool for doing that.