Using data pump IMPDP utility we can generate SQL or DDL/DML from the dump file using SQLFILE option. When you execute IMPDP with SQLFILE option it won’t import the data into the actual tables or into the schema. Suppose if you wanted to generate some particular DDL's from the database you can use this option. Please find the example below with all syntax's.
PARFILE
userid=system/pwd
directory=DATAPUMP
dumpfile=TEST.dmp
logfile=TEST.log
SCHEMAS=TEST
$ expdp parfile=TEST.par
;;;
Export: Release 11.2.0.3.0 - Production on Tue Dec 15 16:28:57 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** parfile=TEST.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.621 GB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."TEST_AAA" 153.7 MB 1644172 rows
. . exported "TEST"."TEST_BBB" 142.0 MB 1571767 rows
. . exported "TEST"."TEST_CCC" 116.7 MB 1107338 rows
. . exported "TEST"."TEST_DDD" 118.2 MB 1459331 rows
. . exported "TEST"."TEST_EEE" 75.05 MB 771091 rows
. . exported "TEST"."TEST_FFF" 50.99 MB 487267 rows
. . exported "TEST"."TEST_GGG" 51.08 MB 616332 rows
. . exported "TEST"."TEST_HHH" 49.66 MB 591722 rows
. . exported "TEST"."TEST_III" 45.65 MB 514947 rows
. . exported "TEST"."TEST_JJJ" 43.33 MB 577712 rows
. . exported "TEST"."TEST_KKK" 41.89 MB 563463 rows
. . exported "TEST"."TEST_LLL" 40.29 MB 498193 rows
. . exported "TEST"."TEST_MMM" 40.73 MB 470865 rows
. . exported "TEST"."TEST_NNN" 39.70 MB 428088 rows
. . exported "TEST"."TEST_OOO" 32.45 MB 313492 rows
. . exported "TEST"."TEST_PPP" 35.83 MB 450797 rows
. . exported "TEST"."TEST_QQQ" 33.22 MB 438805 rows
. . exported "TEST"."TEST_RRR" 32.91 MB 427978 rows
. . exported "TEST"."TEST_SSS" 32.46 MB 352338 rows
. . exported "TEST"."TEST_TTT" 31.09 MB 372510 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/mnt05/DATAPUMP/TEST.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:36:46
Do the import with IMPDP utility using SQLFILE option.
$ impdp system/pwd dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log
;;;
Import: Release 11.2.0.3.0 - Production on Thu Dec 17 15:57:49 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 15:58:28
The output of the DDL.sql file would be
-- CONNECT SYSTEM
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
CREATE USER "TEST" IDENTIFIED BY VALUES 'S:793F36205EF4B329188DDEE462889143B8A321D748374746A322B82EAE0F;1ACC9700A60AE94C'
DEFAULT TABLESPACE "APP_DATA01"
TEMPORARY TABLESPACE "TEMP002";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE JOB TO "TEST";
GRANT CREATE PROCEDURE TO "TEST";
GRANT CREATE SEQUENCE TO "TEST";
GRANT CREATE VIEW TO "TEST";
GRANT CREATE TABLE TO "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST";
GRANT CREATE SESSION TO "TEST";
CREATE TABLE "TEST"."TEST_AAA"
( "ID" NUMBER,
"MEM_NBR" VARCHAR2(75 BYTE),
"MEMBER_DATE" DATE,
"COMPLIANCE" VARCHAR2(6 BYTE),
"RNK" NUMBER,
"RVAL" CHAR(12 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "APP_DATA01" ;
CREATE TABLE "TEST"."TEST_CCC"
( "ID" NUMBER,
"MEM_NBR" VARCHAR2(75 BYTE),
"MEMBER_DATE" DATE,
"COMPLIANCE" VARCHAR2(6 BYTE),
"RNK" NUMBER,
"RVAL" CHAR(7 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "APP_DATA01" ;
CREATE TABLE "TEST"."TEST_BBB"
( "ID" NUMBER,
"MEM_NBR" VARCHAR2(75 BYTE),
"MEMBER_DATE" DATE,
"COMPLIANCE" VARCHAR2(6 BYTE),
"RNK" NUMBER,
"RVAL" CHAR(7 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "APP_DATA01" ;
No comments:
Post a Comment