如何用expdp、impdp按表空间导出、导入？

参考：http://blog.csdn.net/zftang/article/details/6387325

A数据库：

表空间：ylcois

用户名：ylcois

密码：ylcois

B数据库：

表空间：dbo_ylcois

用户名：dbo_ylcois

密码：oracle

从A数据库导出表空间ylcois，再导入到B数据库的表空间dbo_ylcois里，步骤：

1.A数据库建立导出文件目录

SQL> create or replace directory expdir as 'd:exp';
 
Directory created
 
SQL> grant read,write on directory expdir to public;
 
Grant succeeded
 
SQL> select * from dba_directories;

2.导出空间ylcois

Expdp ylcois/ylcois@orcl dumpfile=ylcois.dmp tablespaces=ylcois logfile=exp.log directory=expdir job_name=my_job

3.B数据库建立导入目录

SQL> create or replace directory dump_dir 'c:pump_dir';
 
create or replace directory dump_dir 'c:pump_dir'
 
ORA-00905: 缺失关键字
 
SQL> create or replace directory dump_dir as 'c:pump_dir';
 
Directory created
 
SQL> select * from dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            DUMP_DIR                       c:pump_dir
SYS                            SUBDIR                         E:appAdministratorproduct11.2.0dbhome_3demoschemaorder_entry/2002/Sep
SYS                            SS_OE_XMLDIR                   E:appAdministratorproduct11.2.0dbhome_3demoschemaorder_entry
SYS                            LOG_FILE_DIR                   E:appAdministratorproduct11.2.0dbhome_3demoschemalog
SYS                            DATA_FILE_DIR                  E:appAdministratorproduct11.2.0dbhome_3demoschemasales_history
SYS                            XMLDIR                         c:adeaime_dadvfh0169oracle/rdbms/xml
SYS                            MEDIA_DIR                      E:appAdministratorproduct11.2.0dbhome_3demoschemaproduct_media
SYS                            DATA_PUMP_DIR                  E:appAdministrator/admin/orcl/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR          E:appAdministratorproduct11.2.0dbhome_3/ccr/state
 
9 rows selected

4.建立表空间和用户

create tablespace dbo_ylcois
logging  
datafile 'E:appAdministratororadataorcldbo_ylcois.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 

create user dbo_ylcois identified by oracle
default tablespace dbo_ylcois; 

--给用户授权
grant connect,resource,dba to dbo_ylcois;

grant connect,resource,create any view,create any synonym,create database link to dbo_ylcois;

 如果已经有了，先删除用户和空间，再新建

SQL> drop user dbo_ylcois cascade;
 
User dropped
 
SQL> drop user dbo_ylcois;
 
drop user dbo_ylcois
 
ORA-01918: 用户 'DBO_YLCOIS' 不存在
 
SQL> drop tablespace ylcois INCLUDING CONTENTS and datafiles;
 
Tablespace dropped

5.把从A数据库导出的文件ylcois.dmp拷贝到B数据库的'c:pump_dir'下面

6.导入B数据库，这里用到remap_tablespace参数

--以下是将ylcois用户下的数据全部导入到表空间dbo_ylcois（原来为ylcois表空间下）下

C:UsersAdministrator>impdp dbo_ylcois/oracle@orcl directory=dump_dir dumpfile=
ylcois.dmp remap_tablespace=ylcois:dbo_ylcois