Using EXPDP/IMPDP to transfer table spaces

----Source Library Prod

Sql> select name from V$tablespace; NAME------------------------------systemsysauxundotbs1userstemptemp1testtest1exptest9 rows selected. Sql> Select Userenv (' LANGUAGE ') from DUAL; USERENV (' LANGUAGE ')----------------------------------------------------American_america. Al32utf8

---Target library catdb

[[email protected] ~]$ sqlplus Sys/[email protected] as Sysdbasql*plus:release 11.2.0.4.0 Production on Sat 4 15:16:2  4 2015Copyright (c) 1982, 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

--Migrate the Source Library PROD test table space to the target library CatDB

Tablespace self-contained prod for-------detection

Sql> EXECUTE Dbms_tts. Transport_set_check (' Test ', true);P L/sql procedure successfully completed. Sql> SELECT * from Transport_set_violations;no rows selected

--Modify the table space to read only state

sql> alter TABLESPACE test Read only; Tablespace altered.

--Using a data pump to transfer table space

[[email protected] ~]$ expdp system/[email protected] 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 Library transport table space 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                    &Nbsp;   /u01/app/oracle/oradata/prod/exptest101.dbf9 rows selected. [[email protected] ~]$ cp /u01/app/oracle/oradata/test01_new.dbf /u01/app/oracle/ Oradata/catdb/test01.dbf[[email protected] ~]$ cp /u01/app/oracle/oradata/prod/test02.dbf  /u01/app/oracle/oradata/catdb/test02.dbf

--Import table spaces with IMPDP on the target library

[[email protected] ~]$ impdp system/[email protected] 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

--Validation

[[Email protected] ~]$ sqlplus sys/[email protected] 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/[email protected]; 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 table space of the source and target libraries to read write mode

sql> conn/as sysdbaconnected.sql> select name from V$database; NAME---------prodsql> alter tablespace test read write; Tablespace altered. Sql> Conn Sys/[email protected] As sysdbaconnected.sql> select name from V$database; NAME---------catdbsql> alter tablespace test read write; Tablespace altered. Sql>

Migration last completed O (∩_∩) o~

This article is from the "DBA Sky" blog, so be sure to keep this source http://kevinora.blog.51cto.com/9406404/1671325

Using EXPDP/IMPDP to transfer table spaces