传输表空间：
自包含：
1、使用SQL*PLUS，RMAN，Data Pump工具实现手动的传输表空间。
2、使用EM工具中的传输表空间向导实现传输表空间。
三、跨平台传输表空间
从oracle 10g开始，oracle实现了跨平台的表空间传输，跨平台的意味着数据库可以从一种类型的平台迁移到另一中类型的平台上，大多数（但不是全部）的平台都支持传输表空间。首先必须通过查看v$transportable_platform视图查看oracle支持的平台，并确定每种平台的字节存储次序(字节存储机制），
注意：这一点非常重要。以下查询为oracle支持的各种平台及字节存储次序（版本为10.2.0.4），在跨平台表空间传输时，需要通过查询该视图进行平台和字节存储次序的比对。
col platform_name for a30
select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
示例：
--然后通过数据库查询oracle10g,11g等支持的平台转换，如下：
SQL> select * from v$transportable_platform order by platform_name;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------ --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
19 rows selected.
--可以在源库在数据导出后进行转换，如下；
--或者也可以在目标库导入数据之前进行转换，如下：
跨平台传输表空间的简要操作步骤
1) 确定平台的 Endian 格式
2) 确保表空间为自包含并使其只读(如果利用rman操作，可不用将表空间至于只读)
3) 用 exp、expdp等实用程序导出元数据
4) 转换数据文件以匹配 Endian 格式 ( 若一致可跳过）
5) 拷贝文件到目标系统
6) 使用 imp导入实用程序导入元数据
7）将源库、目标库的传输的表空间read write；
传输表空间
|
|
源库 |
目标库 |
|
IP 地址 |
192.168.10.2 |
192.168.10.3 |
|
实例名 |
ORA11GR2 |
PROD |
1) 【 在 ORA11GR2 实例】 创建目录对象
SYS@ORA11GR2>create or replace directory dir_ora11gr2 as '/home/oracle';
Directory created.
——授权可以使用目录对象的用户：
SYS@ORA11GR2>grant read,write on directory dir_ora11gr2 to public;
Grant succeeded.
2) 【 在 ORA11GR2 实例】 创建测试表空间 ts_ora11gr2 及测试用户 xxf 和测试表 t_ora11gr2
SYS@ORA11GR2>create tablespace ts_ora11gr2 datafile '/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf' size 20m;
Tablespace created.
SYS@ORA11GR2>create user xxf identified by xxf account unlock;
User created.
SYS@ORA11GR2>grant connect,create table to xxf;
Grant succeeded.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t_ora11gr2 tablespace ts_ora11gr2 as select * from all_objects;
create table t_ora11gr2 tablespace ts_ora11gr2 as select * from all_objects
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TS_ORA11GR2'
解决：
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>alter user xxf quota unlimited on TS_ORA11GR2;
User altered.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t_ora11gr2 tablespace ts_ora11gr2 as select * from all_objects;
Table created.
XXF@ORA11GR2>select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_ORA11GR2 TS_ORA11GR2
--检查是否违反自包含：
XXF@ORA11GR2>exec sys.dbms_tts.transport_set_check('TS_ORA11GR2',true);
PL/SQL procedure successfully completed.
XXF@ORA11GR2>select * from sys.transport_set_violations;
no rows selected
XXF@ORA11GR2>
3) 【在 ORA11GR2 实例】 将表空间 ts_ora11gr2 改为只读模式
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>alter tablespace ts_ora11gr2 read only;
Tablespace altered.
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_ORA11GR2 READ ONLY
4) 【在 ORA11GR2 实例】 利用数据泵导出表空间 ts_ora11gr2 结构信息
SYS@ORA11GR2>exit
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@wang ~]$ expdp system/oracle dumpfile=ts_ora11gr2.dmp directory=dir_ora11gr2 transport_tablespaces=ts_ora11gr2 transport_full_check=y
Export: Release 11.2.0.4.0 - Production on Mon Oct 3 11:10:03 2016
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/******** dumpfile=ts_ora11gr2.dmp directory=dir_ora11gr2 transport_tablespaces=ts_ora11gr2 transport_full_check=y
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:
/home/oracle/ts_ora11gr2.dmp
******************************************************************************
Datafiles required for transportable tablespace TS_ORA11GR2:
/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Oct 3 11:11:02 2016 elapsed 0 00:00:57
5) 【在 PROD 实例】创建目录对象及授权
SYS@PROD>create or replace directory dir_pord as '/home/oracle';
Directory created.
SYS@PROD>grant read,write on directory dir_prod to public;
Grant succeeded.
6) 将【 ORA11GR2 实例】数据泵导出的 dmp 文件 scp 到【 PROD 实例】 /home/oracle 目录下
[oracle@bing ~]$ scp 192.168.10.2:/home/oracle/ts_ora11gr2.dmp .
oracle@192.168.10.2's password:
ts_ora11gr2.dmp 100% 104KB 104.0KB/s 00:00
[oracle@bing ~]$ pwd
/home/oracle
[oracle@bing ~]$ ls
ts_ora11gr2.dmp
[oracle@bing ~]$
7) 将 【ORA11GR2 实 例 】 表 空 间 ts_ora11gr2 的 数 据 文 件 scp 到 【PROD实 例 】/u01/app/oracle/oradata/PROD 目录下
[oracle@bing ~]$ cd /u01/app/oracle/oradata/PROD/
[oracle@bing PROD]$ pwd
/u01/app/oracle/oradata/PROD
[oracle@bing PROD]$ scp 192.168.10.2:/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf .
oracle@192.168.10.2's password:
ts_ora11gr2_01.dbf 100% 20MB 20.0MB/s 00:00
[oracle@bing PROD]$ ls ts_ora11gr2_01.dbf
ts_ora11gr2_01.dbf
[oracle@bing PROD]$
8) 【在PROD实例】利用数据泵导入表空间 ts_ora11gr2结构信息，并将表空间 ts_ora11gr2 中的对象映射到 hr 用户下
[oracle@bing ~]$ impdp system/oracle dumpfile=ts_ora11gr2.dmp directory=dir_prod transport_datafiles=/u01/app/oracle/oradata/PROD/ts_ora11gr2_01.dbf remap_schema="(xxf:hr)"
Import: Release 11.2.0.4.0 - Production on Mon Oct 3 12:12:24 2016
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/******** dumpfile=ts_ora11gr2.dmp directory=dir_prod transport_datafiles=/u01/app/oracle/oradata/PROD/ts_ora11gr2_01.dbf remap_schema=(xxf:hr)
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 Mon Oct 3 12:12:28 2016 elapsed 0 00:00:03
9) 【在PROD实例】验证传输是否成功
SYS@PROD>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_ORA11GR2 READ ONLY
7 rows selected.
SYS@PROD>select owner,table_name,tablespace_name from dba_tables where owner='HR' and table_name='T_ORA11GR2';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ---------- --------------------------------------------
HR T_ORA11GR2 TS_ORA11GR2
SYS@PROD>select count(*) from hr.t_ora11gr2;
COUNT(*)
----------
68316
10) 收尾工作【PROD实例】、【 ORA11GR2 实例】
【 PROD 实例】
SYS@PROD>alter tablespace ts_ora11gr2 read write;
Tablespace altered.
SYS@PROD>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_ORA11GR2 ONLINE
【 ORA11GR2 实例】
SYS@ORA11GR2>alter tablespace ts_ora11gr2 read write;
Tablespace altered.
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_ORA11GR2 ONLINE
完成！！！！！！！！！！！！！！！！！！！！！！！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2126693/ ，如需转载，请注明出处，否则将追究法律责任。
