advertisements
_____________________________________________________________________________________________________________________
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 DDLs from the database you can use this option. Please find the example below with all syntaxes.
$ expdp scott/tiger directory=exp_dir dumpfile=scott.dmp logfile=scott.log
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 21:16:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=exp_dir dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 307.5 MB
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/SEQUENCE/SEQUENCE
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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 262.2 MB 2251900 rows
. . exported "SCOTT"."EXAMPLE_PARTITION" 837.4 KB 95120 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P1" 441.3 KB 49999 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2" 408.3 KB 45121 rows
. . exported "SCOTT"."DEPT" 5.945 KB 4 rows
. . exported "SCOTT"."EMP1" 5.875 KB 2 rows
. . exported "SCOTT"."EMP2" 5.890 KB 3 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:21:25
Do the import with impdp utility using sqlfile option. The argument value must be a file name where the sqls will get spooled from the dump file. Better use with .sql extension.
$ impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql
Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 21:34:36
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_FULL_01": scott/******** directory=exp_dir dumpfile=scott.dmp sqlfile=script.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/SEQUENCE/SEQUENCE
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 21:34:53
The content of the scripts.sql file would be like this.
-- CONNECT SCOTT
ALTER SESSION SET EDITION = "ORA$BASE";
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
ALTER SESSION SET EDITION = "ORA$BASE";
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:D846EA3EB87287A3AED08AF38EB0B4F640F49A9A4A972108BF3917B769;DB1B37F84BDF15E6'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" 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
ALTER SESSION SET EDITION = "ORA$BASE";
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD9.DIAMOND.COM', inst_scn=>
'11626845804212');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
CREATE SEQUENCE "SCOTT"."SEQSCOTT" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
;
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."EXAMPLE"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"UID" VARCHAR2(40 BYTE),
"PIX" VARCHAR2(40 BYTE),
"FNAME" VARCHAR2(100 BYTE),
"MNAME" VARCHAR2(100 BYTE),
"LNAME" VARCHAR2(100 BYTE),
"SFIX" VARCHAR2(40 BYTE),
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment