使用expdp/impdp传输表空间

----源库 prod

SQL> select name from v$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPTEMP1TESTTEST1EXPTEST9 rows selected.SQL> select userenv('LANGUAGE') FROM DUAL;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

---目标库 catdb

[oracle@ora11g ~]$ sqlplus sys/oracle@catdb as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 15:16:24 2015Copyright (c) 1982, 2013, Oracle.  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 optionsSQL> select name from v$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTEXPTEST18 rows selected.SQL> select userenv('LANGUAGE') FROM dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

--迁移源库prod test表空间到目标库catdb

-------检测tablespace 的自包含 prod

SQL>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('test',true);PL/SQL procedure successfully completed.SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;no rows selected

--修改表空间为read only状态

SQL> alter tablespace test read only;Tablespace altered.

--使用数据泵传输表空间

[oracle@ora11g ~]$ expdp system/oracle@prod directory=dump dumpfile=expdp_tbs_test_%U.dmp logfile=ttbs.log TRANSPORT_TABLESPACES = testExport: Release 11.2.0.4.0 - Production on Sat Jul 4 20:17:48 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_TRANSPORTABLE_01":  system/********@prod directory=dump dumpfile=expdp_tbs_test_%U.dmp logfile=ttbs.log transport_tablespaces=test Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:  /ora_data/dump/expdp_tbs_test_01.dmp******************************************************************************Datafiles required for transportable tablespace TEST:  /u01/app/oracle/oradata/prod/test02.dbf  /u01/app/oracle/oradata/test01_new.dbfJob "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 4 20:18:50 2015 elapsed 0 00:01:01

------将源库传输表空间的datafile 传送到另一个库上

SQL> select tablespace_name, file_name from dba_data_files;TABLESPACE_NAME                FILE_NAME------------------------------ --------------------------------------------------USERS                          /u01/app/oracle/oradata/prod/users01.dbfUNDOTBS1                       /u01/app/oracle/oradata/prod/undotbs01.dbfSYSAUX                         /u01/app/oracle/oradata/prod/sysaux01.dbfSYSTEM                         /u01/app/oracle/oradata/prod/system01.dbfTEST                           /u01/app/oracle/oradata/test01_new.dbfTEST                           /u01/app/oracle/oradata/prod/test02.dbfTEST1                          /u01/app/oracle/oradata/prod/test1.dbfEXPTEST                        /u01/app/oracle/oradata/prod/exptest01.dbfEXPTEST1                       /u01/app/oracle/oradata/prod/exptest101.dbf9 rows selected.[oracle@ora11g ~]$ cp /u01/app/oracle/oradata/test01_new.dbf /u01/app/oracle/oradata/catdb/test01.dbf[oracle@ora11g ~]$ cp /u01/app/oracle/oradata/prod/test02.dbf /u01/app/oracle/oradata/catdb/test02.dbf

--在目标库上用impdp导入表空间

[oracle@ora11g ~]$ impdp system/oracle@catdb directory=dump dumpfile=expdp_tbs_test_%U.dmp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/catdb/test01.dbf','/u01/app/oracle/oradata/catdb/test02.dbf'Import: Release 11.2.0.4.0 - Production on Sat Jul 4 20:29:22 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 optionsMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@catdb directory=dump dumpfile=expdp_tbs_test_%U.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/catdb/test01.dbf,/u01/app/oracle/oradata/catdb/test02.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 4 20:29:28 2015 elapsed 0 00:00:06

--验证

[oracle@ora11g ~]$ sqlplus sys/oracle@catdb as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 20:34:55 2015Copyright (c) 1982, 2013, Oracle.  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 optionsSQL> select name from v$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTEXPTEST1TEST9 rows selected.SQL> conn scott/oracle@catdb;Connected.SQL> select table_name, tablespace_name from user_tables where tablespace_name='TEST';TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------T1                             TESTTEST                           TESTT6                             TESTT3                             TESTT1_OLD                         TESTEMPLOYEE                       TESTEMP                            TESTDEPT                           TEST8 rows selected.

--最后记得把源库和目标库的test表空间设置为read write模式

SQL> conn / as sysdbaConnected.SQL> select name from v$database;NAME---------PRODSQL> alter tablespace test read write;Tablespace altered.SQL> conn sys/oracle@catdb as sysdbaConnected.SQL> select name from v$database;NAME---------CATDBSQL> alter tablespace test read write;Tablespace altered.SQL>

迁移最后完成O(∩_∩)O~