Generate Oracle objects DDL using dbms_metadata.get_ddl

December 22, 2005 · Filed under Oracle

Since Oracle 9i, Oracle added a new utility called dbms_metadata.get_ddl. This utility provides you with object DDL generated out of Oracle Data Dictionary. It is extremely handy and useful.

For example, you can use:

set long 500000
select dbms_metadata.get_ddl(’TABLESPACE’,'TablespaceName’) from dual;

to get a particular tablespace’s DDL. You can also use it for users, role, and other objects.

To get a table’s DDL, use:

set long 500000
select dbms_metadata.get_ddl(’TABLE’,'TableName’,'SchemaName’) from dual;

Thanks to a fellow DBA for this great tip. You know who you are:)

Leave a Comment