1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

dbms_metadata.get_ddl for entire schema's scripts

Discussion in 'SQL PL/SQL' started by lovelandj, Feb 12, 2009.

  1. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    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 :rolleyes:
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why can't you use 'Export' and 'Import' utility ? You can go for Datapump also.
    Export and Import
     
  3. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    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

    Code (Text):
    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
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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 :

    Code (Text):
    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.
     
  5. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    hi Rajavu, thanks for the solution, the problem is solved. Appreciate it very much :)