Hello everyone,
In this article, we will learn Generating DDL using existing dumpfile.
To generate DDL of a schema or table from a dumpfile we use a parameter called SQLFILE.
Purpose:
SQLFILE is a paramater which can be used in IMPDP utility to generate DDL of schema or table .
Syntax :
|
1 |
SQLFILE=directory_name:FILENAME.sql |
Demonstration :
Step 1:
Perform an full schema export of SCOTT.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[oracle@oracle ~]$ expdp directory=dpump dumpfile=SCOTT_03022021.dmp logfile=SCOTT_03022021.log job_name=SCOTT schemas=SCOTT Export: Release 12.1.0.2.0 - Production on Wed Feb 3 04:32:33 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SCOTT": /******** AS SYSDBA directory=dpump dumpfile=SCOTT_03022021.dmp logfile=SCOTT_03022021.log job_name=SCOTT schemas=SCOTT Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "SCOTT"."DEPT" 6.031 KB 4 rows . . exported "SCOTT"."EMP" 8.781 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.960 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYS"."SCOTT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SCOTT is: /u01/oradata/avprod/dpump/SCOTT_03022021.dmp Job "SYS"."SCOTT" successfully completed at Wed Feb 3 04:33:10 2021 elapsed 0 00:00:32 |
Step 2:
Drop the SCOTT schema
|
1 2 |
SQL> drop user scott cascade; User dropped. |
Step 3:
Use the SQLFILE parameter while performing IMPDP utility.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@oracle ~]$ impdp directory=dpump dumpfile=SCOTT_03022021.dmp logfile=IMPDP_SCOTT_03022021.dmp schemas=scott SQLFILE=DPUMP_2:scott_DDL.sql Import: Release 12.1.0.2.0 - Production on Wed Feb 3 04:41:20 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_SQL_FILE_SCHEMA_01": /******** AS SYSDBA directory=dpump dumpfile=SCOTT_03022021.dmp logfile=IMPDP_SCOTT_03022021.dmp schemas=scott SQLFILE=DPUMP_2:scott_DDL.sql Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Wed Feb 3 04:41:26 2021 elapsed 0 00:00:02 |
Note : By using SQLFILE parameter in IMPDP untilty , The data will not be import to database, it just creates generate DDL in specified directory.
Step 4 :
Check the DDL script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
[oracle@oracle ~]$ cd /u01/oradata/avprod/dpump2 [oracle@oracle dpump2]$ cat scott_DDL.sql -- CONNECT SYS ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/USER -- CONNECT SYSTEM CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:60B84D49A84264F82912AA2E591BC02C793F708D4CA11A67ECCC5BAB464F;H:63EC639D32F2C215B68C56321EE236FE;T:6A9CE68BC27775DC45F195A89032E9BEA7F3B363C063376C801B6D347CD665249C3FF0FD557EA9589329A1AC86F55D89C869802000F5D97E51843DF394936B0A90ED2E8B13DE650E4EE5710E1A88FBA0;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT UNLIMITED TABLESPACE TO "SCOTT"; -- new object type path: SCHEMA_EXPORT/ROLE_GRANT GRANT "CONNECT" TO "SCOTT"; GRANT "RESOURCE" TO "SCOTT"; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE ALTER USER "SCOTT" DEFAULT ROLE ALL; -- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA -- CONNECT SCOTT BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'AVPROD', inst_scn=>'1847823'); COMMIT; END; / -- new object type path: SCHEMA_EXPORT/TABLE/TABLE -- CONNECT SYS CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14 BYTE), "LOC" VARCHAR2(13 BYTE) ) SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."BONUS" ( "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "SAL" NUMBER, "COMM" NUMBER ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."SALGRADE" ( "GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER ) SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT SCOTT BEGIN NULL; END; / BEGIN NULL; END; / -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYS ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ) ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ) ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS -- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER |
Step 5:
We can execute the script to Create schema with objects(Metadata).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[oracle@oracle dpump2]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 3 04:52:02 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> @scott_DDL.sql Session altered. Session altered. Session altered. Session altered. Session altered. Session altered User created. Grant succeeded. Grant succeeded. Grant succeeded. User altered. PL/SQL procedure successfully completed. Table created. Table created. Table created. Table created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Table altered. Table altered. Table altered. |
Please test above document in you LAB environment .
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
