dbms_metadata tips

Starting with Oracle9i, the new utility package, called dbms_metadata, can be used to  easily display DDL and stored procedures directly from the data dictionary. Using dbms_metadata, you can punch individual objects or an entire Oracle schema. Best of all, dbms_metadata 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.

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;

Punching PL/SQL from Oracle

The dbms_metadata utility is also used for punching source code, PL/SQL, and Java that is stored inside Oracle packages. Stored procedures or functions can also easily be extracted using dbms_metadata.

In the example in Listing D, I extract all procedures associated with the fred schema.
 

Listing D:

connect fred/flintstone;


 spool procedures_punch.lst


 select
   DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name)
 from
   user_objects u
 where
   object_type = 'PROCEDURE';
 

 spool off;

The dbms_metadata utility is especially good for extracting procedural code because all of the original formatting is retained. Listing E is an example of the output from dbms_metadata for stored procedures.

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.

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan).  Buy direct from the publisher and save 30%!

 

 

---