Generate the DDL of entire schema objects with DBMS_METADATA package

Generate the DDL of entire schema objects with DBMS_METADATA package

We will use the DBA_OBJECTS table for specify the parameter input to get all the DDL of all objects present in the schema.

Following script will give you the ddl of object present in the Schema.


SPOOL E:\DDL.SQL
set pagesize 0
set long 90000
set feedback off
set echo off
set heading off
set lines 100

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

select dbms_metadata.GET_DDL(u.object_type,u.object_name,u.owner) from
dba_objects u
where owner = 'Schema_name';
SPOOL OFF

Note:
1. You can also disabled storage clause in defination of Oracle by executing following statement before generated DDLs

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

2. Enable the SQL Terminator after each object syntax completed.

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

Example:
Showing the two object present in the list

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> set heading off
SQL> set lines 100
SQL>
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SQL>
SQL> select dbms_metadata.GET_DDL(u.object_type,u.object_name,u.owner) from
  2  dba_objects u
  3  where owner = 'TEST';

  CREATE TABLE "TEST"."TESTPRIMARY"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(10),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;


  CREATE TABLE "TEST"."TESTING"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;


  CREATE UNIQUE INDEX "TEST"."SYS_C008201" ON "TEST"."TESTPRIMARY" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.