----源库 prod

SQL>selectnamefromv$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPTEMP1TESTTEST1EXPTEST9rowsselected.SQL>selectuserenv('LANGUAGE')FROMDUAL;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

---目标库 catdb

[oracle@ora11g~]$sqlplussys/oracle@catdbassysdbaSQL*Plus:Release11.2.0.4.0ProductiononSatJul415:16:242015Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selectnamefromv$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTEXPTEST18rowsselected.SQL>selectuserenv('LANGUAGE')FROMdual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

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

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

SQL>EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('test',true);PL/SQLproceduresuccessfullycompleted.SQL>SELECT*FROMTRANSPORT_SET_VIOLATIONS;norowsselected

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

SQL>altertablespacetestreadonly;Tablespacealtered.

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

[oracle@ora11g~]$expdpsystem/oracle@proddirectory=dumpdumpfile=expdp_tbs_test_%U.dmplogfile=ttbs.logTRANSPORT_TABLESPACES=testExport:Release11.2.0.4.0-ProductiononSatJul420:17:482015Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsStarting"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":system/********@proddirectory=dumpdumpfile=expdp_tbs_test_%U.dmplogfile=ttbs.logtransport_tablespaces=testProcessingobjecttypeTRANSPORTABLE_EXPORT/PLUGTS_BLKProcessingobjecttypeTRANSPORTABLE_EXPORT/TABLEProcessingobjecttypeTRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessingobjecttypeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMastertable"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfullyloaded/unloaded******************************************************************************DumpfilesetforSYSTEM.SYS_EXPORT_TRANSPORTABLE_01is:/ora_data/dump/expdp_tbs_test_01.dmp******************************************************************************DatafilesrequiredfortransportabletablespaceTEST:/u01/app/oracle/oradata/prod/test02.dbf/u01/app/oracle/oradata/test01_new.dbfJob"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfullycompletedatSatJul420:18:502015elapsed000:01:01

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

SQL>selecttablespace_name,file_namefromdba_data_files;TABLESPACE_NAMEFILE_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.dbf9rowsselected.[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~]$impdpsystem/oracle@catdbdirectory=dumpdumpfile=expdp_tbs_test_%U.dmpTRANSPORT_DATAFILES='/u01/app/oracle/oradata/catdb/test01.dbf','/u01/app/oracle/oradata/catdb/test02.dbf'Import:Release11.2.0.4.0-ProductiononSatJul420:29:222015Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsMastertable"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"successfullyloaded/unloadedStarting"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":system/********@catdbdirectory=dumpdumpfile=expdp_tbs_test_%U.dmpTRANSPORT_DATAFILES=/u01/app/oracle/oradata/catdb/test01.dbf,/u01/app/oracle/oradata/catdb/test02.dbfProcessingobjecttypeTRANSPORTABLE_EXPORT/PLUGTS_BLKProcessingobjecttypeTRANSPORTABLE_EXPORT/TABLEProcessingobjecttypeTRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessingobjecttypeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"successfullycompletedatSatJul420:29:282015elapsed000:00:06

--验证

[oracle@ora11g~]$sqlplussys/oracle@catdbassysdbaSQL*Plus:Release11.2.0.4.0ProductiononSatJul420:34:552015Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selectnamefromv$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPCATALOGTBSEXPTESTEXPTEST1TEST9rowsselected.SQL>connscott/oracle@catdb;Connected.SQL>selecttable_name,tablespace_namefromuser_tableswheretablespace_name='TEST';TABLE_NAMETABLESPACE_NAME------------------------------------------------------------T1TESTTESTTESTT6TESTT3TESTT1_OLDTESTEMPLOYEETESTEMPTESTDEPTTEST8rowsselected.

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

SQL>conn/assysdbaConnected.SQL>selectnamefromv$database;NAME---------PRODSQL>altertablespacetestreadwrite;Tablespacealtered.SQL>connsys/oracle@catdbassysdbaConnected.SQL>selectnamefromv$database;NAME---------CATDBSQL>altertablespacetestreadwrite;Tablespacealtered.SQL>

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