如何用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 createdSQL> grant read,write on directory expdir to public;Grant succeededSQL> 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:\app\Administrator\product\11.2.0\dbhome_3\demo\schema\order_entry\/2002/Sep
SYS                            SS_OE_XMLDIR                   E:\app\Administrator\product\11.2.0\dbhome_3\demo\schema\order_entry\
SYS                            LOG_FILE_DIR                   E:\app\Administrator\product\11.2.0\dbhome_3\demo\schema\log\
SYS                            DATA_FILE_DIR                  E:\app\Administrator\product\11.2.0\dbhome_3\demo\schema\sales_history\
SYS                            XMLDIR                         c:\ade\aime_dadvfh0169\oracle/rdbms/xml
SYS                            MEDIA_DIR                      E:\app\Administrator\product\11.2.0\dbhome_3\demo\schema\product_media\
SYS                            DATA_PUMP_DIR                  E:\app\Administrator/admin/orcl/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR          E:\app\Administrator\product\11.2.0\dbhome_3/ccr/state
 
9 rows selected

4.建立表空间和用户

create tablespace dbo_ylcois
logging
datafile 'E:\app\Administrator\oradata\orcl\dbo_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 droppedSQL> drop user dbo_ylcois;drop user dbo_ylcoisORA-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:\Users\Administrator>impdp dbo_ylcois/oracle@orcl directory=dump_dir dumpfile=
ylcois.dmp remap_tablespace=ylcois:dbo_ylcois