The Oracle9i get_ddl function   Donald K. Burleson

It has always been a huge pain to punch the DDL for tables, indexes and stored procedures into a flat file. Oracle9i now has a dbms_metadata package with a get_ddl function to copy DDL syntax out of the dictionary.

With all of the new storage clauses and advanced parameters, getting table and index definitions has always been a huge problem.  Hence, prior to Oracle9i, the DBA was generally forced to keep the DDL source code in a special library.  This makes life difficult because the DBA is now forced to maintain and manage versions of tables and index DDL separately from the data dictionary.

Oracle9i, the DBA will be able to keep all table and index definitions inside the data dictionary (where they belong), and use the get_ddl function to punch-out a copy whenever they need to migrate the object.

Below we see that the get_ddl function is very simple to use, only requiring the object_type and the object_name as import parameter.  Also, make sure to set linesize to a large value, because get_ddl returns a CLOB datatype, and you want SQL*Plus to be able to display the result set.

Set lines 90000

Spool sales_table_ddl.sql

Select dbms_metadata.get_ddl(‘TABLE’,’SALES’) from dual;

Spool off;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm