1、创建DIRECTORY
创新互联公司专业为企业提供
雨花网站建设、雨花做网站、雨花网站设计、雨花网站制作等企业网站建设、网页设计与制作、雨花企业网站模板建站服务，10年
雨花做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。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 1000
SQL> 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_dp
WRITE DIR_DP /fol/dir_dp
READ 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/state
WRITE ORACLE_OCM_CONFIG_DIR /fol/app/oracle/product/11.2.0/db_1/ccr/state
READ ORACLE_OCM_CONFIG_DIR2 /fol/app/oracle/product/11.2.0/db_1/ccr/state
WRITE ORACLE_OCM_CONFIG_DIR2 /fol/app/oracle/product/11.2.0/db_1/ccr/state
READ 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/Sep
WRITE 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/xml
19 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 2015
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "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_DATA
Total estimation using BLOCKS method: 10.18 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing 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 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/fol/dir_dp/expdp_scott1.dmp
Job "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=USER
Import: Release 11.2.0.4.0 - Production on Fri Sep 11 16:40: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.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "LILC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "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_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/TABLE/TABLE
Processing 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 rows
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "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 2015
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "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_DATA
Total estimation using BLOCKS method: 20.06 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/TABLE/TABLE
. . exported "TEST"."T" 16.81 MB 173576 rows
. . exported "TEST"."T2" 19.25 KB 74 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/fol/dir_dp/test.dmp
Job "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.dmp
remap_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 2015
Copyright (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 Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "LILC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "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/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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T" 16.81 MB 173576 rows
. . imported "TEST"."T2" 19.25 KB 74 rows
Job "LILC"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 11 17:53:32 2015 elapsed 0 00:00:05
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文题目：expdp和impdp使用之一（不同用户和不用表空间）-创新互联
网页URL：
http://cdkjz.cn/article/dshpcc.html
