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.
After Oracle9i, you can use the dbms_metadata
utility package will easily display DDL and
stored procedures directly from the data dictionary. Using
this powerful utility, you can punch individual objects or an
entire Oracle schema. Best of all, it is easy to use. You
simply execute dbms_metadata.get_ddl, specify the
object names, and Oracle will extract ready-to-use DDL.
To punch off all table and indexes for the EMP table, execute
dbms_metadata. get_ddl, select from DUAL, and provide
all required parameters as shown in Listing A:
Listing A:
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;
The output is shown below.
The only thing missing is the ending
semicolons after each statement. Just for illustration, I'm
showing how a primary key can be punched as part of the table
DDL or separately using the INDEX argument.
Note that you have complete table and index definitions,
including storage parameters (e.g., pctfree, pctused,
freelists, and freelist groups) as well as
tablespace storage and buffer pool directives.
For large migrations, you can modify the dbms_metadata
syntax to punch a whole schema. As you can see in
Listing C, it is easily done by selecting
dbms_metadata. get_ddl and specifying USER_TABLES and
USER_INDEXES. This syntax will punch all table and index
definitions for the entire schema, in this example, the
scott schema.
Listing C:
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;
