Get table and index DDL in the easy way(转）

Get table and index DDL the easy way

Don Burleson

Prior to Oracle9i, getting table and index DDL was a time-consuming and tricky process. You could run the export utility with ROWS=NO, but the output was hard to re-use because of quoted strings. The only other option was to write complex dictionary scripts that might not work on complex objects such as IOT and nested tables.

Punching DDL from the dictionary is very useful when you are migrating a system to a new platform and you want to pre-create the objects in a new tablespace so that you can import with IGNORE=Y.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;

When you have foreign keys (“references” constraints), it would be nice to punch the table DDL in their proper order for re-loading into another database. If you do not order the table, a table may be added that references another table that has not yet been added. Making this change would involve adding a WHERE clause that queries dba_constraints and dba_cons_columns to determine the proper table order.