CNDBA社区
1.什么是传输表空间
传输表空间技术始于Oracle9i，它的原理是将元数据和数据文件从一个数据库迁移到另一个数据库，而且不需要原库和目标库具有相同的块大小，相比较导入导出工具，传输表空间只会复制包含实际数据的数据库文件到目标数据库的指定位置，所以会节省很多时间，传输表空间也是数据库之间传输大数据的一种方法。
2.传输表空间的要求
支持传输表空间技术的操作系统平台（传输表空间可以跨平台，但需要符合要求）
SQL> col PLATFORM_NAME for a50
SQL> col ENDIAN_FORMAT FOR A10
SQL> select * from v$transportable_platform order by 1;
PLATFORM_ID PLATFORM_NAME ENDIAN_FOR CON_ID
----------- -------------------------------------------------- ---------- ----------
1 Solaris[tm] OE (32-bit) Big 0
2 Solaris[tm] OE (64-bit) Big 0
3 HP-UX (64-bit) Big 0
4 HP-UX IA (64-bit) Big 0
5 HP Tru64 UNIX Little 0
6 AIX-Based Systems (64-bit) Big 0
7 Microsoft Windows IA (32-bit) Little 0
8 Microsoft Windows IA (64-bit) Little 0
9 IBM zSeries Based Linux Big 0
10 Linux IA (32-bit) Little 0
11 Linux IA (64-bit) Little 0
12 Microsoft Windows x86 64-bit Little 0
13 Linux x86 64-bit Little 0
15 HP Open VMS Little 0
16 Apple Mac OS Big 0
17 Solaris Operating System (x86) Little 0
18 IBM Power Based Linux Big 0
19 HP IA Open VMS Little 0
20 Solaris Operating System (x86-64) Little 0
21 Apple Mac OS (x86-64) Little 0
22 Linux OS (S64) Big 0
21 rows selected.
其他要求：
1）原库和目标库的字符集必须兼容。
2）目标库不能存在同名表空间。
3）如果要传输的表空间中存在物化视图，分区表等潜在的对象，这些对象一定要包含在表空间集中。
4）不能传输系统表空间，也不能传输包含sys用户对象的表空间。
3.传输表空间的模拟实验
3.1 在原库上创建要传输的表空间：
SQL> create tablespace trans_tbs datafile '/oradata/ORCLCDB/trans_tbs1.dbf' size 1024m;
Tablespace created.
SQL> create table trantest1 tablespace trans_tbs as select * from all_objects;
Table created.
SQL> select table_name,tablespace_name from dba_tables where tablespace_name='TRANS_TBS';
TABLE_NAME TABLESPACE_NAME
TRANTEST1 TRANS_TBS
3.3 查看原库和目标库的平台信息
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> SELECT d.name,d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
NAME PLATFORM_NAME ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
ORCL Linux x86 64-bit Little
目标端:
SQL> SELECT d.name,d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
NAME PLATFORM_NAME ENDIAN_FOR
--------------------------- -------------------------------------------------- ----------
ORCL Linux x86 64-bit Little
3.4 表空间自包含检查：
用来检测相关对象的属性是否都存在在一个表空间。使用自包含检测的包。
SQL >exec dbms_tts.transport_set_check('TRANS_TBS',true,true);
PL/SQL procedure successfully completed.
#可以一次检查多个表空间，使用逗号隔开即可。
SQL >select * from transport_set_violations;
no rows selected
#查看检测结果。没有结果就是通过。
3.5 创建传输表空间集：
SQL> alter tablespace trans_tbs read only;
Tablespace altered.
[oracle@node01 ~]$ expdp /'sys//wwwwww as sysdba/' directory=dpdata dumpfile=tbs_tran.dmp transport_tablespaces=trans_tbs
Export: Release 12.1.0.2.0 - Production on Fri Sep 9 23:13:03 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=dpdata dumpfile=tbs_tran.dmp transport_tablespaces=trans_tbs
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
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:
/backup/tbs_tran.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANS_TBS:
/oradata/ORCLCDB/trans_tbs1.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Sep 9 23:13:29 2022 elapsed 0 00:00:23
3.6 将传输表空间集和数据文件传输到目标库相应路径：
[oracle@node01 backup]$ scp tbs_tran.dmp 192.168.56.101:/backup/
The authenticity of host '192.168.56.101 (192.168.56.101)' can't be established.
ECDSA key fingerprint is SHA256:v7xP3L9tauyg2ec9QiT1YI2x0qPh8VJ8kxDoscqMxSQ.
ECDSA key fingerprint is MD5:96:58:8d:ca:c5:6f:1a:08:b8:f9:03:27:03:a9:1d:34.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.101' (ECDSA) to the list of known hosts.
oracle@192.168.56.101's password:
tbs_tran.dmp 100% 124KB 20.6MB/s 00:00
[oracle@node01 backup]$ scp /oradata/ORCLCDB/trans_tbs1.dbf 192.168.56.101:/oradata/orcl
oracle@192.168.56.101's password:
trans_tbs1.dbf 100% 1024MB 63.9MB/s 00:16
impdp /'sys//wwwwww as sysdba/' directory=dpdata dumpfile=tbs_tran.dmp
将原库与目标库的表空间重新设置为读写：
SQL >alter tablespace trans_tbs read write;
Tablespace altered.
SQL >alter tablespace trans_tbs read write;
Tablespace altered.
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
