Extracting DDL from Oracle: 2 Approaches

Posted at Wednesday, February 06, 2008
In the past, the most practical approach to extracting DDL from Oracle was either to use a 3rd-party tool and/or write a ton of dynamic SQL. I like using Quest's TOAD product, but it's also useful to know the alternatives.

Approach 1: Use the DBMS_METADATA package

Example:

Here is a script that first creates a temporary table, then creates a PL/SQL procedure that makes various calls to DBMS_METADATA. Reference the Oracle docs to get the most out of this package: DBMS_METADATA package reference. In my example, I show how to use this method to extract the DDL for all the directory objects in the database (i.e. DBA_DIRECTORIES). Of course, I could have extracted just about anything.

Contents of sample_ddl_extract.sql:

DROP TABLE my_metadata;
CREATE TABLE my_metadata(md CLOB);

CREATE OR REPLACE PROCEDURE sample_ddl_extract
AS
hndl NUMBER; --dbms_metadata handle
th NUMBER; --transform handle
DDL CLOB; --individual clobs extracted from the database
BEGIN
hndl := DBMS_METADATA.OPEN ('DATABASE_EXPORT'); --Open the metadata
DBMS_METADATA.set_filter (hndl, 'INCLUDE_PATH_EXPR', '=''DIRECTORY'''); --Filter data as appropriate
th := DBMS_METADATA.add_transform (hndl, 'DDL'); --Get the Transform Handle
DBMS_METADATA.set_transform_param (th, 'SQLTERMINATOR', TRUE); --Include the semicolon

LOOP
DDL := DBMS_METADATA.fetch_clob (hndl); --Loop through the result set, inserting into our temp table
EXIT WHEN DDL IS NULL;

INSERT INTO my_metadata
(md)
VALUES (DDL);
COMMIT;
END LOOP;
DBMS_METADATA.CLOSE (hndl);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
show errors

Run the example:

[/tmp cubs2@rac2]$ sqlplus "/ as sysdba" @/tmp/sample_ddl_extract.sql

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 6 15:19:18 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


Table dropped.


Table created.


Procedure created.

No errors.

Query the results via:

exec sample_ddl_extract
set echo off
set long 9000000
set longc 900
set lines 400
set trimspool on
set pages 0
set feedback off
set head off
set sqlblanklines off
spool /tmp/sample_ddl_extract.out
select * from my_metadata;
spool off;

Examine the results of the output file, sample_ddl_extract.out:

[/tmp cubs2@rac2]$ more sample_ddl_extract.out
SQL> select * from my_metadata;

CREATE OR REPLACE DIRECTORY "DBCAPTURE" AS '/u02/app/oradata/cubs/admin/dbcapture';



CREATE OR REPLACE DIRECTORY "DATA_PUMP_DIR" AS '/u02/app/oradata/cubs/admin/dp';



CREATE OR REPLACE DIRECTORY "DBREPLAY" AS '/u02/app/oradata/cubs/admin/dbreplay';



CREATE OR REPLACE DIRECTORY "ORACLE_OCM_CONFIG_DIR" AS '/u03/app/oracle/product/db/11g/ccr/state';



CREATE OR REPLACE DIRECTORY "AUDIT_DIR" AS '/tmp/';



CREATE OR REPLACE DIRECTORY "IDR_DIR" AS '/u02/app/oradata/cubs/admin/log/diag/rdbms/cubs/cubs2/ir';



CREATE OR REPLACE DIRECTORY "XMLDIR" AS '/u03/app/oracle/product/db/11g/rdbms/xml';



GRANT READ ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";



GRANT WRITE ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";



GRANT READ ON DIRECTORY "DBCAPTURE" TO "SYSTEM";



GRANT WRITE ON DIRECTORY "DBCAPTURE" TO "SYSTEM";



GRANT READ ON DIRECTORY "DBREPLAY" TO "SYSTEM";



GRANT WRITE ON DIRECTORY "DBREPLAY" TO "SYSTEM";



GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";



GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";


SQL> spool off;

Approach 2: Use the DATAPUMP utilities

Example:

In this example I show how to accomplish the exact same result as in Approach 1. The only difference here is that we use the DATAPUMP utilities. Contents of sample_ddl_extract.bsh:

#!/bin/bash
export ORACLE_SID=cubs2;
. oraenv
sqlplus -S /nolog <<EOF
CONNECT / AS SYSDBA;
COL OBJECT_PATH FORMAT A20;
COL COMMENTS FORMAT A80;
SET LINES 200;
SELECT * FROM DATABASE_EXPORT_OBJECTS WHERE OBJECT_PATH='DIRECTORY';
EOF
$ORACLE_HOME/bin/expdp \"/ as sysdba\" reuse_dumpfiles=y include=directory full=y content='METADATA_ONLY' directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp

$ORACLE_HOME/bin/impdp \"/ as sysdba\" directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp sqlfile=data_pump_dir:directory.sql

Run the example:

[/tmp cubs2@rac2]$ ./sample_ddl_extract.bsh
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle

OBJECT_PATH COMMENTS N
-------------------- -------------------------------------------------------------------------------- -
DIRECTORY Directories and their dependent grants and audits Y


Export: Release 11.1.0.6.0 - Production on Wednesday, 06 February, 2008 15:29:27

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" reuse_dumpfiles=y include=directory full=y content=METADATA_ONLY directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u02/app/oradata/cubs/admin/dp/metadata.expdp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:29:41


Import: Release 11.1.0.6.0 - Production on Wednesday, 06 February, 2008 15:29:42

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp sqlfile=data_pump_dir:directory.sql
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 15:29:46

Examine the results of the output file, /u02/app/oradata/cubs/admin/dp/directory.sql:

[/tmp cubs2@rac2]$ more /u02/app/oradata/cubs/admin/dp/directory.sql
-- CONNECT SYS
ALTER SESSION SET EDITION = "ORA$BASE";
-- new object type path: DATABASE_EXPORT/DIRECTORY/DIRECTORY
CREATE DIRECTORY "DBCAPTURE" AS '/u02/app/oradata/cubs/admin/dbcapture';

CREATE DIRECTORY "DATA_PUMP_DIR" AS '/u02/app/oradata/cubs/admin/dp';

CREATE DIRECTORY "DBREPLAY" AS '/u02/app/oradata/cubs/admin/dbreplay';

CREATE DIRECTORY "ORACLE_OCM_CONFIG_DIR" AS '/u03/app/oracle/product/db/11g/ccr/state';

CREATE DIRECTORY "AUDIT_DIR" AS '/tmp/';

CREATE DIRECTORY "XMLDIR" AS '/u03/app/oracle/product/db/11g/rdbms/xml';

-- new object type path: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT READ ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";

GRANT WRITE ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";

GRANT READ ON DIRECTORY "DBCAPTURE" TO "SYSTEM";

GRANT WRITE ON DIRECTORY "DBCAPTURE" TO "SYSTEM";

GRANT READ ON DIRECTORY "DBREPLAY" TO "SYSTEM";

GRANT WRITE ON DIRECTORY "DBREPLAY" TO "SYSTEM";

GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";

GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";

Consult DATABASE_EXPORT_OBJECTS and SCHEMA_EXPORT_OBJECTS to see which object types are available for similar metadata exports.

Here is a zip archive from this example: ddl_extract_sample.zip

[/tmp cubs2@rac2]$ unzip -l ddl_extract_sample.zip
Archive: ddl_extract_sample.zip
Length Date Time Name
-------- ---- ---- ----
539 02-06-08 14:49 sample_ddl_extract.bsh
949 02-06-08 15:18 sample_ddl_extract.sql
1145 02-06-08 15:25 sample_ddl_extract.out
1080 02-06-08 15:29 directory.sql
-------- -------
3713 4 files


Labels: ,