Tabs

Tuesday, December 30, 2014

Expdp and Impdp

EXPDP and IMPDP on Tables


Set ORACLE_SID=ORCL
SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 29 13:32:04 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE OR REPLACE DIRECTORY test_dir AS 'D:\app\Backup\Bkp';
Directory created.
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Grant succeeded.
SQL> CONN SCOTT/tiger
Connected.
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB               MGR HIREDATE         SAL     DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ----------
      7788 SCOTT      ANALYST          7566 19-APR-87       3000         20
      3839 KING       PRESIDENT             17-NOV-81       5000         10
      7844 TURNER     SALESMAN         7698 08-SEP-81       1500         30
      7876 ADAMS      CLERK            7788 23-MAY-87       1100         20
      7900 JAMES      CLERK            7698 03-DEC-81        950         30
      7902 FORD       ANALYST          7566 03-DEC-81       3000         20
      7934 MILLER     CLERK            7782 23-JAN-82       1300         10

7 rows selected.
SQL> SELECT * FROM DEPT;

    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

D:\>expdp SCOTT/tiger@ORCL directory=dpdir dumpfile=DB10G.dmp logfile=expdpDB10G.log

Export: Release 11.2.0.1.0 - Production on Mon Dec 29 16:31:56 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  SCOTT/********@ORCL directory=dpdir dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                             5.937 KB       4 rows
. . exported "SCOTT"."EMP"                              7.835 KB       7 rows
. . exported "SCOTT"."MURALI"                               0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\APP\BACKUP\DB10G.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:33:01

SQL> drop table emp purge;
Table dropped.
SQL> drop table dept purge;
Table dropped.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dept;
select * from dept
              *
ERROR at line 1:
ORA-00942: table or view does not exist

D:\>impdp scott/tiger@ORCL tables=emp,dept DIRECTORY=dpdir dumpfile=DB10G.dmp LOGFILE=impdpEMP_DEPT.log

Import: Release 11.2.0.1.0 - Production on Mon Dec 29 16:44:26 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/********@ORCL tables=emp,dept DIRECTORY=dpdir dumpfile=DB10G.dmp LOGFILE=impdpEMP_DEPT.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . imported "SCOTT"."EMP"                               7.835 KB       7 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:44:30

EXPDP and IMPDP on SCHEMA


 D:\>EXPDP scott/tiger@ORCL schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log 

Export: Release 11.2.0.1.0 - Production on Mon Dec 29 16:51:05 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@orcl schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               7.835 KB       7 rows
. . exported "SCOTT"."MURALI"                                0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\APP\BACKUP\BKP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:51:41

D:\>IMPDP scott/tiger@ORCL schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log 


Import: Release 11.2.0.1.0 - Production on Mon Dec 29 16:54:16 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/********@orcl schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . imported "SCOTT"."EMP"                               7.835 KB       7 rows
. . imported "SCOTT"."MURALI"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:54:18
Posted by at
Labels:

No comments:

Post a Comment

Thank you for your Suggestions...

Subscribe to: Post Comments (Atom)