Get Oracle schema syntax the easy way

August 6, 2003

[From Builder.com]

Oracle professionals must frequently "punch," or extract, table and index definitions from Oracle and move them to different systems. Extracting Data Definition Language (DDL) for Oracle schema objects (e.g., tables, indexes, stored procedures, triggers, sequences, etc.) from the dictionary is very useful when you're migrating a system to a new platform and you want to pre-create the objects in a new tablespace.

Generally, in an Oracle migration, the schema and DDL are created in the target database, and then the rows are imported into the new database using the Oracle imp utility with the IGNORE=Y parameter. The IGNORE=Y parameters tells Oracle to use the new DDL definitions instead of the DDL inside the import data file.

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

The article continues at http://builder.com.com/5100-6388_14-5054021.html