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" ;
Smart way of Technology 