IMPDP工具的传输表空间完成数据迁移

本文通过实践演示，完整模拟一下使用EXPDP/IMPDP工具实现传输表空间的过程，供参考。
任务描述：将secdb1实例上的sec用户表空间tbs_sec的数据传输到secdb2实例上secooler用户下。1.secdb1实例环境准备
1）创建待操作的表空间
sys@secdb1> create tablespace tbs_sec datafile "/u01/app/Oracle/oradata/secdb1/dfile/tbs_sec_01.dbf" size 5 m autoextend on;Tablespace created.2）重新创建sec用户并授权
sys@secdb1> drop user sec cascade;User dropped.sys@secdb1> create user sec identified by sec default tablespace tbs_sec;User created.sys@secdb1> grant connect,resource to sec;Grant succeeded.3）在sec用户下简单创建一张表并初始化一条记录
sys@secdb1> conn sec/sec
Connected.
sec@secdb1> create table t（x int）;Table created.sec@secdb1> insert into t values （1）;1 row created.sec@secdb1> commit;Commit complete.sec@secdb1> select * from t; X
----------
12.检tbs_sec表空间是否“自包含”
sys@secdb1> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK（"tbs_sec",true）;PL/SQL procedure successfully completed.sys@secdb1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;no rows selectedOK，没有问题。3.将表空间调整到只读状态
sys@secdb1> ALTER TABLESPACE tbs_sec read only;Tablespace altered.4.使用EXPDP工具完成表空间元素据导出
sys@secdb1> create directory dir_home as "/home/oracle";Directory created.sys@secdb1> grant read,write on directory dir_home to public;Grant succeeded.[oracle@seclinux ~]$ expdp system/oracle1 directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=yExport: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:23:21Copyright （c） 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tbs_sec.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:23:395.将tbs_sec表空间对应的数据文件拷贝到secdb2实例对应的目录
[oracle@seclinux ~]$ cp /u01/app/oracle/oradata/secdb1/dfile/tbs_sec_01.dbf /u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf6.在secdb2实例上使用IMPDP工具完成表空间的导入
sys@secdb2> create user secooler identified by secooler;User created.sys@secdb2> grant connect,resource to secooler;Grant succeeded.sys@secdb2> create directory dir_home as "/home/oracle";Directory created.sys@secdb2> grant read,write on directory dir_home to public;Grant succeeded.[oracle@seclinux ~]$ impdp system/oracle1 DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=（sec:secooler）Import: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:34:48Copyright （c） 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=（sec:secooler）
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:34:527.在secdb2实例上验证数据导入效果
sys@secdb2> conn secooler/secooler
Connected.
secooler@secdb2> select * from cat;TABLE_NAME TABLE_TYPE
------------------------------ -----------
T TABLEsecooler@secdb2> select * from t; X
----------
1sys@secdb2> select owner,tablespace_name from dba_tables where wner="SECOOLER" and table_name="T";OWNER TABLESPACE_NAME
------------------------------ ------------------------------
SECOOLER TBS_SECOK，使命完成。8.最后记得将secdb1实例中tbs_sec表空间置为可读写模式
sys@secdb1> ALTER TABLESPACE tbs_sec read write;Tablespace altered.9.小结
使用EXPDP/IMPDP传输表空间功能可以比较便捷高效的完成数据迁移，善用之。
有关使用EXP/IMP工具完成传输表空间的操作方法，请参考如下文章，大同小异。
《【实验】利用可传输表空间技术实现数据的高效迁移》（http://www.linuxidc.com/Linux/2016-08/133898.htm）更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址