expdp 和impdp使用之一（不同用户和不用表空间）

1、创建DIRECTORY

SQL> create or replace directory dir_dp as '/fol/dir_dp';Directory created.

2、授权

SQL> grant read,write on directory dir_dp to scott;Grant succeeded.

3.查看目录及权限

SQL> set lines 200 pagesize 1000SQL> SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;PRIVILEGE       DIRECTORY_NAME                 DIRECTORY_PATH--------------- ------------------------------ ----------------------------------------------------------------------------------------------------READ            DATA_FILE_DIR                  /fol/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/READ            DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/READ            DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/WRITE           DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/WRITE           DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/READ            DIR_DP                         /fol/dir_dpWRITE           DIR_DP                         /fol/dir_dpREAD            LOG_FILE_DIR                   /fol/app/oracle/product/11.2.0/db_1/demo/schema/log/WRITE           LOG_FILE_DIR                   /fol/app/oracle/product/11.2.0/db_1/demo/schema/log/READ            MEDIA_DIR                      /fol/app/oracle/product/11.2.0/db_1/demo/schema/product_media/READ            ORACLE_OCM_CONFIG_DIR          /fol/app/oracle/product/11.2.0/db_1/ccr/stateWRITE           ORACLE_OCM_CONFIG_DIR          /fol/app/oracle/product/11.2.0/db_1/ccr/stateREAD            ORACLE_OCM_CONFIG_DIR2         /fol/app/oracle/product/11.2.0/db_1/ccr/stateWRITE           ORACLE_OCM_CONFIG_DIR2         /fol/app/oracle/product/11.2.0/db_1/ccr/stateREAD            SS_OE_XMLDIR                   /fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/WRITE           SS_OE_XMLDIR                   /fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/READ            SUBDIR                         /fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/SepWRITE           SUBDIR                         /fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep                XMLDIR                         /fol/app/oracle/product/11.2.0/db_1/rdbms/xml19 rows selected.SQL> select DEFAULT_TABLESPACE from dba_users where username='SCOTT';DEFAULT_TABLESPACE------------------------------USERS

4.执行导出

expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile =expdp_scott1.dmp logfile=expdp_scott1.log;$ expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile =expdp_scott1.dmp logfile=expdp_scott1.log;Export: Release 11.2.0.4.0 - Production on Fri Sep 11 16:02:49 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@CPP schemas=scott directory=dir_dp dumpfile=expdp_scott1.dmp logfile=expdp_scott1.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 10.18 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."TEST"                              8.414 MB   86785 rows. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows. . exported "SCOTT"."EMP"                               8.570 KB      14 rows. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows. . exported "SCOTT"."BONUS"                                 0 KB       0 rowsMaster table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:  /fol/dir_dp/expdp_scott1.dmpJob "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 11 16:03:28 2015 elapsed 0 00:00:37

目标库：

1、创建测试表空间和用户

SQL> create tablespace LLC datafile '+DATA/phub/datafile/LLC01.dbf' size 5G;Tablespace created.SQL> create user lilc identified by lilc default tablespace LLC;User created.SQL> grant dba to lilc;Grant succeeded.SQL> conn lilc/lilc;Connected.SQL> select DEFAULT_TABLESPACE from dba_users where username='LILC';DEFAULT_TABLESPACE------------------------------LLC

2.创建DIRECTORY

SQL> create or replace directory dir_dp as '/home/oracle/dir_dp';Directory created.

3.授权

SQL> grant read,write on directory dir_dp to lilc;

4.执行导入：

更换表空间和更换用户导入：

[oracle@cwogg dir_dp]$ impdp lilc/lilc directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude=USERImport: Release 11.2.0.4.0 - Production on Fri Sep 11 16:40:57 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsMaster table "LILC"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "LILC"."SYS_IMPORT_FULL_01":  lilc/******** directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude=USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "LILC"."TEST"                               16.81 MB  173570 rows. . imported "LILC"."DEPT"                               5.937 KB       4 rows. . imported "LILC"."EMP"                                8.570 KB      14 rows. . imported "LILC"."SALGRADE"                           5.867 KB       5 rows. . imported "LILC"."BONUS"                                  0 KB       0 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "LILC"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 11 16:41:05 2015 elapsed 0 00:00:07

目标库上没有相同的用户导出和导入：

$ expdp system/123123@CPP schemas=test directory=dir_dp dumpfile =test.dmp logfile=test.log;Export: Release 11.2.0.4.0 - Production on Fri Sep 11 17:32:57 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@CPP schemas=test directory=dir_dp dumpfile=test.dmp logfile=test.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20.06 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLE. . exported "TEST"."T"                                  16.81 MB  173576 rows. . exported "TEST"."T2"                                 19.25 KB      74 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:  /fol/dir_dp/test.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 11 17:33:06 2015 elapsed 0 00:00:07

导入：（更改用户的默认表空间）

[oracle@cwogg dir_dp]$ impdp lilc/lilc directory=dir_dp DUMPFILE=test.dmpremap_schema=test:test remap_tablespace=test:LLC logfile=test.log;Import: Release 11.2.0.4.0 - Production on Fri Sep 11 17:53:26 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsMaster table "LILC"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "LILC"."SYS_IMPORT_FULL_01":  lilc/******** directory=dir_dp DUMPFILE=test.dmp remap_schema=test:test remap_tablespace=test:LLC logfile=test.log Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."T"                                  16.81 MB  173576 rows. . imported "TEST"."T2"                                 19.25 KB      74 rowsJob "LILC"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 11 17:53:32 2015 elapsed 0 00:00:05