dbms_metadata.get_ddl for entire schema's scripts

[lovelandj]

Hi all

In our oracle database 10g there was a schema called "CUSTOMER" that owned a lot of tables, sequences, indexes, procedures etc. I want to recreate the "CUSTOMER" schema from scratch but unfortunately the scripts are lost and i can't really go on one object after another to find the script and create it. I just discovered that there's something called dbms_metadata.get_ddl for entire schema scripts. Can someone please explain or point out how i can use this for my schema "CUSTOMER".

thanks

[rajavu]

Why can't you use 'Export' and 'Import' utility ? You can go for Datapump also.
Export and Import

[lovelandj]

hi Rajavu thank u for your reply. actually i can't use Export Import because i am not planning to copy all the schema objects. once i have all the DDL scripts i can select which ones i want to create. and then i will create different objects in different schemas.

I searched a little online and apparently one has to use it this way

select  dbms_metadata.get_ddl ('TABLE', 'TABLE_NAME', 'SCHEMA_NAME') from dual

but can i use this in any way to get all the tables, indexes, etc in one statement? thanks

[rajavu]

Yes.. You can do using that . Check this link

You have to use DBMS_METADATA.GET_DDL, GET_DEPENDENT_DDL, SET_TRANSFORM_PARAM etc .

See the example :

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
 
 
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 
 
 
 SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP') FROM DUAL;
 
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP')
--------------------------------------------------------------------------------
 
 
  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 
 
 
 SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'ST
ORAGE', FALSE);
 
PL/SQL procedure successfully completed.
 
 SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TA
BLESPACE', FALSE);
 
PL/SQL procedure successfully completed.
 
 SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
 
 
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 
 
 
 SQL> select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP') from DUAL;
 
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP')
--------------------------------------------------------------------------------
 
 
  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 
 
 
 SQL>

You have to loop through the selected table's list and
EITHER spool the output into file and execute it OR Execute immediate directly.

[lovelandj]

hi Rajavu, thanks for the solution, the problem is solved. Appreciate it very much