Generate Oracle objects DDL using dbms_metadata.get_ddl

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:)

2 responses to “Generate Oracle objects DDL using dbms_metadata.get_ddl”

  1. I need to return SQL SERVER 2005 view’s metadata to my data grid. So, a function like select dbms_metadata.get_ddl(’TABLE’,'TableName’,'SchemaName’) from dual;

    really helps for me.

    Does SQL Server 2005 have the similiar function?

    Thanks in advanced.



  2. Yes, it does.

    The easiest is probably through point-and-click. Go to Management Studio, go to either the database or views (I am not at a computer where I can verify at the moment), and pick the scripting tasks, and you will see the DDL for them.

    If you are into scripting, it is possible to write vbscript and/or PowerShell script to do this.

    Good luck!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.