DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, September 5, 2015

Data Pump and export Parameters-SQLFILE


Data Pump and export Parameters-SQLFILE


SQL>  create index emp_ix on emp(sal);

Index created.

SQL>  select index_name from user_indexes;

INDEX_NAME
------------------------------
EMP_IX
PK_EMP
PK_DEPT


[oracle@oracle11g dump]$ expdp test/test tables=emp directory=test_dir dumpfile=emp.dmp logfile=emp.log

Export: Release 11.2.0.3.0 - Production on Sat Sep 5 17:27:38 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** tables=emp directory=test_dir dumpfile=emp.dmp logfile=emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."EMP"                                8.578 KB      14 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /u01/dump/emp.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 17:27:47






[oracle@oracle11g ~]$ impdp test/test  directory=test_dir dumpfile=emp.dmp sqlfile=test.sql

Import: Release 11.2.0.3.0 - Production on Sat Sep 5 17:26:13 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** directory=test_dir dumpfile=emp.dmp sqlfile=test.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 17:26:15




SQLFILE output


[oracle@oracle11g dump]$ cat test.sql
-- CONNECT TEST
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: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."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 "TEST" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "TEST"."PK_EMP" ON "TEST"."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 "TEST" PARALLEL 1 ;

  ALTER INDEX "TEST"."PK_EMP" NOPARALLEL;
CREATE INDEX "TEST"."EMP_IX" ON "TEST"."EMP" ("SAL")
  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 "TEST" PARALLEL 1 ;

  ALTER INDEX "TEST"."EMP_IX" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "TEST"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX 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 "TEST"  ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'PK_EMP';
  i_o := 'TEST';
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,1,0,14,NV,NV,TO_DATE('2015-09-01 00:13:01',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'EMP_IX';
  i_o := 'TEST';
  EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,14,1,12,1,1,1,0,14,NV,NV,TO_DATE('2015-09-05 17:20:24',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "TEST"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "TEST"."DEPT" ("DEPTNO") ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE
  c varchar2(60);
  nv varchar2(1);
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
  s varchar2(60) := 'TEST';
  t varchar2(60) := 'EMP';
  p varchar2(1);
  sp varchar2(1);
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,
               14,4,39,14,0,NULL,NULL,NULL,
               TO_DATE('2015-09-01 00:13:01',df));
  c := 'EMPNO';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               14,.0714285714285714,14,14,0,7369,7934,4,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'C24A46','C25023',nv,2,nv;
  c := 'ENAME';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               14,.0714285714285714,14,14,0,3.38883673419062E+35,4.53054701071074E+35,6,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'4144414D53','57415244',nv,2,nv;
  c := 'JOB';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               5,.2,5,14,0,3.39086497213261E+35,4.32285038678150E+35,8,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'414E414C595354','53414C45534D414E',nv,2,nv;
  c := 'MGR';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               6,.166666666666667,6,13,1,7566,7902,4,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'C24C43','C25003',nv,2,nv;

END;
/

DECLARE
  c varchar2(60);
  nv varchar2(1);
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
  s varchar2(60) := 'TEST';
  t varchar2(60) := 'EMP';
  p varchar2(1);
  sp varchar2(1);
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  NULL;
  c := 'HIREDATE';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               13,.0769230769230769,13,14,0,2444591,2446939,8,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'77B40C11010101','77BB0517010101',nv,2,nv;
  c := 'SAL';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               12,.0833333333333333,12,14,0,810,5010,4,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'C2090B','C2330B',nv,2,nv;
  c := 'COMM';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               4,.25,4,4,10,0,1400,2,0,nv,nv,
               TO_DATE('2015-09-01 00:13:01',df),'80','C20F',nv,2,nv;
  c := 'DEPTNO';
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               3,.0357142857142857,3,14,0,10,30,3,1,3,10,
               TO_DATE('2015-09-01 00:13:01',df),'C10B','C11F',nv,2,nv;
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               3,.0357142857142857,3,14,0,10,30,3,1,8,20,
               TO_DATE('2015-09-01 00:13:01',df),'C10B','C11F',nv,2,nv;
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               3,.0357142857142857,3,14,0,10,30,3,1,14,30,
               TO_DATE('2015-09-01 00:13:01',df),'C10B','C11F',nv,2,nv;

  DBMS_STATS.IMPORT_TABLE_STATS('"TEST"','"EMP"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/







No comments: