About
Oracle dbms_metadata
You can use the new utility package called
dbms_metadata that 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;
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;
Issues with dbms_metadata
Like all new Oracle utilities, there are
some shortcomings with dbms_metadata. When
you have tables with foreign key referential
integrity (e.g., Oracle’s references
constraints), it would be nice to have
dbms_metadata punch the table DDL in its
proper order for reloading into another
database. If you don't punch the tables in
the proper order of foreign key referential
integrity, 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.
Despite the immediate shortcomings, the
dbms_metadata utility is fantastic for
extracting DDL and procedural code from
Oracle. I have no doubt a future release of
Oracle will make dbms_metadata even more
powerful.
Related dbms_metadata
Articles:
Using the dbms_metadata package
DBMS_METADATA package tips
Get Oracle schema DDL syntax with
dbms_metadata
Get table and index DDL the easy way
|
|
