共计 7877 个字符，预计需要花费 20 分钟才能阅读完成。
这篇文章主要为大家展示了“数据库中如何实现表空间传输”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“数据库中如何实现表空间传输”这篇文章吧。
1、确保源端、目标端的字符集一致
select userenv(language) from dual;
The source and the destination databases must use compatible database character sets
源和目标数据库必须使用兼容的数据库字符集
The database character sets of the source and the target databases are the same.
源数据库和目标数据库的数据库字符集相同。
The source database character set is a strict (binary) subset of the target database character set
源数据库字符集是目标数据库字符集的严格（二进制）子集
The source and the target databases must use compatible national character sets
源数据库和目标数据库必须使用兼容的国家字符集
2、确保源端表空间不包含 SYS 对象，在目标端也建立这些 OWNER
select OWNER from dba_segments where TABLESPACE_NAME= XX
You cannot transport a tablespace to a destination database that contains a tablespace of the same name
不能将表空间传输到包含相同名称的表空间的目标数据库
You cannot transport the SYSTEM tablespace or objects owned by the user SYS
您不能传输 SYSTEM 表空间或用户 SYS 拥有的对象
3、查询源端、目标端的字节序
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
If the source platform and the destination platform are of different endianness, then an additional step must be done on either the source or destination platform to convert the tablespace being transported to the destination format
如果源平台和目标平台具有不同的字节顺序，则必须在源平台或目标平台上执行额外的步骤，将正在传输的表空间转换为目标格式
4、查询源端表空间是否 self-contained
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(XX
SELECT * FROM TRANSPORT_SET_VIOLATIONS;– 查询结果为空，表示是 self-contained
5、查询源端表空间对应的数据文件
select FILE_NAME from dba_data_files where TABLESPACE_NAME= XX
6、源端设置表空间只读并导出格式文件
SQL ALTER TABLESPACE XX READ ONLY;
expdp system/123456 directory=data_pump_dir transport_tablespaces=XX dumpfile=expdpXX.dmp
7、如果源端和目标的字节序一致，则拷贝第 6 步的 expdpXX.dmp 到目标端 data_pump_dir 对应的目录, 拷贝源端表空间对应的数据文件至目标端比如 c:\app\orauser\oradata\orawin\XX.dbf
8、如果源端和目标的字节序不一致，则源端执行 rman convert tablespace，再拷贝第 6 步的 expdpXX.dmp 和 /tmp/%U 的数据文件到目标端，expdpXX.dmp 拷贝到目标端的 data_pump_dir 对应的目录，/tmp/%U 的数据文件拷贝到目标端 dba_data_files.file_name 对应的目录
RMAN CONVERT TABLESPACE XX TO PLATFORM Microsoft Windows IA (32-bit) FORMAT /tmp/%U
如上假如目标端的字节序为 Microsoft Windows IA (32-bit)，/tmp/%U 就是存放 XX 表空间转换后的数据文件
9、源端表空间设置回去 read write
ALTER TABLESPACE XX READ WRITE;
10、目标端导入表空间
impdp system/123456 directory=data_pump_dir dumpfile=expdpXX.dmp transport_datafiles= c:\app\orauser\oradata\orawin\XX.dbf
11、检查（EM 做的话，源端默认使用副本导出，目标端默认选择 read wirte，所以源端默认都是 ONLINE 操作，使用命令的话，源端目标端都要手工设置为 read write）
源端：select STATUS from dba_tablespaces where TABLESPACE_NAME= XX – 结果必须为 ONLINE，为 READ ONLY 的话，就要设置为 read write
目标端：
select STATUS from dba_tablespaces where TABLESPACE_NAME= XX – 结果必须为 ONLINE，为 READ ONLY 的话，就要设置为 read write
select OWNER from dba_segments where TABLESPACE_NAME= XX
备注：当然，如果字节序不一样，源端拷贝到目标端的数据文件没有经过第 8 步，也可以在目标端执行 rman convert tablespace
RMAN CONVERT DATAFILE c:\app\orauser\oradata\orawin\XX.dbf TO PLATFORM= Microsoft Windows IA (32-bit) FROM PLATFORM= Solaris[tm] OE (32-bit)
或直接如下，不用管源端是什么
RMAN CONVERT DATAFILE c:\app\orauser\oradata\orawin\XX.dbf TO PLATFORM= Microsoft Windows IA (32-bit)
实验步骤_使用命令模式（源端目标端的 OS 一样的，所以字节序一样）
1、源端 prod2、目标端 TDB 的字符集一样
SQL show parameter db_name
NAME TYPE VALUE
—— —– —–
db_name string prod2
SQL select userenv(language) from dual;
USERENV(LANGUAGE)
————————–
AMERICAN_AMERICA.AL32UTF8
SQL show parameter db_name
NAME TYPE VALUE
——– ——— ——–
db_name string TDB
SQL select userenv(language) from dual;
USERENV(LANGUAGE)
———————-
AMERICAN_AMERICA.AL32UTF8
2、源端要传输的表空间是 PRO2017，表空间对象的用户没有 SYS，在目标端不存在这个表空间
SQL select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME= PRO2017
TABLESPACE_NAME
—————–
PRO2017
SQL select OWNER from dba_segments where TABLESPACE_NAME= PRO2017
OWNER
————-
PRO2017
SQL select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME= PRO2017 – 目标端没有结果
no rows selected
3、源端确保是 self-contained
SQL EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(PRO2017
PL/SQL procedure successfully completed.
SQL SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
4、查询源端表空间对应的数据文件
SQL select FILE_NAME from dba_data_files where TABLESPACE_NAME= PRO2017
FILE_NAME
——————————————————————————–
/mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf
5、源端设置表空间只读并导出格式文件
SQL ALTER TABLESPACE PRO2017 READ ONLY;
[oracle@mestest 2]$ expdp system/123456 directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp
Export: Release 11.2.0.4.0 – Production on Thu Oct 19 05:01:04 2017
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting SYSTEM . SYS_EXPORT_TRANSPORTABLE_01 : system/******** directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table SYSTEM . SYS_EXPORT_TRANSPORTABLE_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp
******************************************************************************
Datafiles required for transportable tablespace PRO2017:
/mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf
Job SYSTEM . SYS_EXPORT_TRANSPORTABLE_01 successfully completed at Thu Oct 19 05:01:33 2017 elapsed 0 00:00:28
6、拷贝格式文件到目标端的 dump 目录，datafile 到目标端的 dba_data_file.file_name 对应目录
scp /mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf oracle@192.168.30.173:/db/oracle/oradata/TDB/
scp /mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp oracle@192.168.30.173:/db/oracle/admin/TDB/dpdump/
7、源端执行
SQL ALTER TABLESPACE PRO2017 READ WRITE;
Tablespace altered.
8、目标端执行，有报错，所以目标端必须建立表空间对应的用户 PRO2017
[oracle@localhost TDB]$ impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles= /db/oracle/oradata/TDB/pro2017.dbf
Import: Release 11.2.0.4.0 – Production on Thu Oct 19 20:33:59 2017
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 successfully loaded/unloaded
Starting SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 : system/******** directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user PRO2017 does not exist in the database
Job SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 stopped due to fatal error at Thu Oct 19 20:34:02 2017 elapsed 0 00:00:02
SQL create user PRO2017 identified by 123456;
User created.
SQL grant connect,resource to PRO2017;
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost TDB]$ impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles= /db/oracle/oradata/TDB/pro2017.dbf
Import: Release 11.2.0.4.0 – Production on Thu Oct 19 20:35:19 2017
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 successfully loaded/unloaded
Starting SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 : system/******** directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job SYSTEM . SYS_IMPORT_TRANSPORTABLE_01 successfully completed at Thu Oct 19 20:35:23 2017 elapsed 0 00:00:02
SQL select tablespace_name,status from dba_tablespaces where TABLESPACE_NAME= PRO2017
TABLESPACE_NAME STATUS
———————————————————— ——————
PRO2017 READ ONLY
SQL ALTER TABLESPACE PRO2017 READ WRITE;
Tablespace altered.
以上是“数据库中如何实现表空间传输”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
