- 02-12-2009 04:33 PM #1
dbms_metadata.get_ddl for entire schema's scripts
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
- Club-Oracle Complementary E-Books and Magazines
Get your free Complementary Copy of Oracle Magazine
You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.
- 02-13-2009 10:15 AM #2
Re: dbms_metadata.get_ddl for entire schema's scripts
Why can't you use 'Export' and 'Import' utility ? You can go for Datapump also.
Export and Import
Raj.
- 02-13-2009 11:29 AM #3
Re: dbms_metadata.get_ddl for entire schema's scripts
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 :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
- 02-16-2009 04:57 AM #4
Re: dbms_metadata.get_ddl for entire schema's scripts
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 :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.
Raj.
- 02-16-2009 06:12 PM #5
Re: dbms_metadata.get_ddl for entire schema's scripts
hi Rajavu, thanks for the solution, the problem is solved. Appreciate it very much
Similar Threads
-
Help on Migration Scripts
By Ravizzz in forum GeneralReplies: 1Last Post: 12-15-2009, 07:13 PM -
how to load entire folder into content server
By nv0044984 in forum Oracle Fusion MiddlewareReplies: 0Last Post: 11-16-2009, 08:02 AM -
Not publishing entire site with all pages
By nv0044984 in forum Oracle Fusion MiddlewareReplies: 0Last Post: 09-17-2009, 05:45 AM -
Using dbms_metadata to extract DDL of all DB objects
By doha in forum SQL PL/SQLReplies: 5Last Post: 06-25-2009, 10:08 AM -
Copy entire Schema from one database to another
By Jamie22 in forum GeneralReplies: 10Last Post: 11-24-2008, 01:50 PM


