Generating lots of DDL with DBMS_METADATA...
Submitted by dave on Thu, 10/09/2008 - 14:51
After using many different tools to generate DDL I usually end up back at DBMS_METADATA. DBMS_METADATA is an Oracle supplied package that became available with Oracle 9. DBMS_METADATA is an API that can be used to generate the DDL of most any object in an Oracle database. It is most handy for getting Table or Index DDL but there is a complete list of objects that DBMS_METADATA handles here.
Table ExampleLog into a schema and run the following to generate DDL for all indexes in the schema:
set pagesize 0 set long 2000000000 spool CreateTables.sql SELECT dbms_metadata.get_ddl('TABLE',table_name) || ';' FROM USER_TABLES ORDER BY TABLE_NAME; spool off
Indexes exampleLog into a schema and run the following to generate DDL for all indexes in the schema:
set pagesize 0 set long 2000000000 spool CreateIndexes.sql SELECT dbms_metadata.get_ddl('INDEX',index_name) || ';' FROM user_indexes ORDER BY TABLE_NAME, INDEX_NAME; spool off