Use expdp/impdp to transport tablespaces

-Source database prod

SQL > select name from vested tablespaceNAMEYSAUXUNDOTBS1USERSTEMP1TESTMP1EXPTEST9 rows selected.SQL > select userenv ('LANGUAGE') FROM DUAL;USERENV (' LANGUAGE')-AMERICAN_AMERICA.AL32UTF8

-Target Library 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 vs. tablespace.NAMEwhichi SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTEXPTEST18 rows selected.SQL > select userenv ('LANGUAGE') FROM dual USERENV ('LANGUAGE')-AMERICAN_AMERICA.AL32UTF8

-- migrate the source database prod test tablespace to the target library catdb

-detect the self-contained prod of tablespace

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

-- modify the tablespace to read only state

SQL > alter tablespace test read only;Tablespace altered.

-- use data pumps to transmit tablespaces

[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

-transfer the datafile of the source database transport tablespace to another library

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

-- Import tablespaces with impdp on the target library

[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

-- Verification

[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 vs. tablespace.NAMEwhichi SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTPTESTST1TEST9 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.

Finally, remember to set the test tablespaces of the source and target databases to read write mode

SQL > conn / as sysdbaConnected.SQL > select name from vault database > alter tablespace test read write;Tablespace altered.SQL > alter tablespace test read write;Tablespace altered.SQL > conn sys/oracle@catdb as sysdbaConnected.SQL > select name from vault database > NAMEMurCATDBSQL > alter tablespace test read write;Tablespace altered.SQL >

Migration finishes O (∩ _ ∩) O ~