Oracle表空间迁移-CSDN博客

step：
--1.在传输前要先检查下这个表空间的依赖关系，看有没有有依赖的对象，执行以下sql就可以了
sys@STATDB2> exec dbms_tts.transport_set_check('BB_DATA2', TRUE);

PL/SQL procedure successfully completed.

--查询返回结果有一个索引表空间有依赖关系
sys@STATDB2> SELECT * FROM transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Index TLBB.PK_bbuser_basic in tablespace bbuser_basic_INDEX enforces primary constriants of table TLBB.bbuser_basic in tables
pace bbuser_basic_DATA

--再查询下索引表空间
sys@STATDB2> exec dbms_tts.transport_set_check('bbuser_basic_INDEX', TRUE);

PL/SQL procedure successfully completed.

--和数据表空间是相关的
sys@STATDB2> SELECT * FROM transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Index TLBB.PK_bbuser_basic in tablespace bbuser_basic_INDEX enforces primary constriants of table TLBB.bbuser_basic in tables
pace bbuser_basic_DATA

Index TLBB.IND_UB_CN in tablespace bbuser_basic_INDEX points to table TLBB.bbuser_basic in tablespace bbuser_basic_DATA
Index TLBB.IND_UB_REGDATE in tablespace bbuser_basic_INDEX points to table TLBB.bbuser_basic in tablespace bbuser_basic_DATA

sys@STATDB2> !
--需要导出表空间中的ddl定义，先看在硬盘空间是否满足
[@stat2.cyou.com ~]$ ll
[@stat2.cyou.com dump_dir]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 5.9G 2.6G 3.0G 46% /
/dev/sdb1 2.6T 1.7T 837G 67% /U01
/dev/sda3 7.8G 571M 6.8G 8% /var
/dev/sda5 12G 2.6G 8.5G 24% /usr
tmpfs 48G 32K 48G 1% /dev/shm
10.10.74.61:/backup/10.11.154.151_statdb2
19T 17T 1.7T 92% /home/oracle/backup_stage/10.11.154.151_statdb2
10.10.75.29:/backup/10.11.17.84_sol_29
3.6T 3.0T 611G 84% /home/oracle/backup_stage/10.11.17.84_sol_29
10.11.54.192:/data/oracle/oradata/statdb2
1.7T 1.3T 327G 80% /U01/mnt_192_data
[@stat2.cyou.com dump_dir]$ exit
@stat2.cyou.com dump_dir]$ exit
exit
--传输表空间必须将表空间设置为read only;

sys@STATDB2> ALTER TABLESPACE bbuser_basic_DATA READ ONLY;

Tablespace altered.

sys@STATDB2> ALTER TABLESPACE bbuser_basic_INDEX READ ONLY;

Tablespace altered.

sys@STATDB2>

--执行导出DDL定义
[@stat2.cyou.com dump_dir]$ expdp \'/ as sysdba\' directory=DATAPUMP_DIR dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log TRANSPORT_TABLESPACES=$bbuser_basic_INDEX,bbuser_basic_DATA$;

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 08 December, 2011 17:19:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": '/******** AS SYSDBA' directory=DATAPUMP_DIR dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log TRANSPORT_TABLESPACES=(bbuser_basic_INDEX,bbuser_basic_DATA)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/dump_dir/bbuser_basic.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:20:05

[@stat2.cyou.com dump_dir]$

--看下目标机器上空间是否满足
[@statdb1.cyou.com statdb1]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 6.0G 2.7G 3.0G 48% /
none 20G 328K 20G 1% /dev/shm
/dev/sda6 120G 360M 113G 1% /opt
/dev/sdb1 3.1T 1.1T 1.9T 38% /U01
/dev/sda5 5.0G 2.8G 2.0G 60% /usr
/dev/sda2 9.9G 836M 8.6G 9% /var
/dev/shm 20G 328K 20G 1% /tmp
10.10.75.29:/backup/10.11.17.84_sol_29
3.6T 3.0T 611G 84% /home/oracle/backup_stage/10.11.17.84_sol_29
10.10.75.29:/backup/10.11.18.149_statdb1
3.6T 3.0T 611G 84% /home/oracle/backup_stage/10.11.18.149_statdb1

--copy
[@stat2.cyou.com dump_dir]$ scp /U01/app/oracle/oradata/statdb2/bbuser_basic_data01.dbf 10.11.18.149:/U01/app/oracle/oradata/statdb1/
oracle@10.11.18.149's password:
bbuser_basic_data01.dbf 100% 20GB 21.1MB/s 16:29
[@stat2.cyou.com dump_dir]$
[@stat2.cyou.com ~]$ scp /U01/app/oracle/oradata/statdb2/bbuser_basic_index01.dbf 10.11.18.149:/U01/app/oracle/oradata/statdb1/
oracle@10.11.18.149's password:
bbuser_basic_index01.dbf 100% 19GB 20.1MB/s 16:27
[@stat2.cyou.com ~]$
--设置表空间read write
sys@STATDB2> ALTER TABLESPACE bbuser_basic_DATA READ WRITE;

Tablespace altered.

sys@STATDB2> ALTER TABLESPACE bbuser_basic_INDEX READ WRITE;

Tablespace altered.
--最后别忘记把ddl定义导过去
[@stat2.cyou.com dump_dir]$ scp bbuser_basic.dmp 10.11.18.149:/home/oracle/out2inner/
oracle@10.11.18.149's password:
bbuser_basic.dmp 100% 112KB 112.0KB/s 00:00
[@stat2.cyou.com dump_dir]$
--在目标机执行导入
[@statdb1.cyou.com out2inner]$ impdp \'/ as sysdba\' directory=DATA_PUMP_out2inner dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log REMAP_SCHEMA=tlbb:ldj TRANSPORT_DATAFILES=$'/U01/app/oracle/oradata/statdb1/bbuser_basic_data01.dbf','/U01/app/oracle/oradata/statdb1/bbuser_basic_index01.dbf'$;

Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 08 December, 2011 17:43:30

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_out2inner dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log REMAP_SCHEMA=tlbb:ldj TRANSPORT_DATAFILES=(/U01/app/oracle/oradata/statdb1/bbuser_basic_data01.dbf,/U01/app/oracle/oradata/statdb1/bbuser_basic_index01.dbf)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 6 error(s) at 17:43:34

--最后别忘记到表空间设置为READ WRITE
sys@statdb1> ALTER TABLESPACE bbuser_basic_DATA READ WRITE;

Tablespace altered.

sys@statdb1> ALTER TABLESPACE bbuser_basic_INDEX READ WRITE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26390465/viewspace-1771320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26390465/viewspace-1771320/