1、源库准备环境

--创建被传输的表空间 create tablespace tts logging datafile '/home/oracle/app/oradata/orcl/tts01.dbf'  size 40m autoextend on next 10m maxsize 100m extent management local;

create tablespace ttsind logging datafile '/home/oracle/app/oradata/orcl/ttsind01.dbf' 

size 40m autoextend on next 10m maxsize 100m extent management local;

--创建用户，并将表空间作为默认表空间

SQL> create user tts identified by tts default tablespace tts; SQL> grant connect,resource to tts; SQL> grant select on dba_objects to tts; [oracle@11g ~]$ sqlplus tts/tts --在该表空间创建表，用于测试 create table datapump as select * from dba_objects; delete from datapump where object_id is null; create index ind_datapump on datapump(object_id) tablespace ttsind; create table datapump02 as select * from dba_objects; create bitmap index ind_datapump02 on datapump02(object_type) tablespace ttsind; commit; SQL> select count(*) from datapump; COUNT(*) ---------- 74886

SQL> select count(*) from datapump02;

COUNT(*) ---------- 74888

2. 源库信息统计

--查询表空间有哪些用户 (sys用户) SQL> select distinct owner from dba_segments where tablespace_name in ('TTS','TTSIND') ;

OWNER

------------------------------ TTS --计算某个表空间所包含对象的大小 select 'SIZE_TABELSPACE' NAME,sum(user_bytes)/1024/1024 SIZE_M from dba_data_files where tablespace_name='TTS' UNION ALL select 'SIZE_OBJECT' NAME,sum(nvl(bytes,0))/1024/1024 SIZE_M from user_segments where tablespace_name='TTS';  NAME SIZE_M --------------- ---------- SIZE_TABELSPACE 39 SIZE_OBJECT -- 查看表空间内对象的个数 select segment_type,count(1) as"NUM" from dba_segments where tablespace_name in ('TTS','TTSIND') group by segment_type; SEGMENT_TYPE NUM ------------------ ---------- TABLE 2 INDEX 2

--查看DBLINK 信息：

SQL> select owner,object_name,object_type from dba_objects where object_type='DATABASELINK';

--查看JOB 信息：

SQL> select count(*) from dba_jobs where schema_user in ('DATAPUMP'); ('','')

3. 检查源、目标平台Endianness

--在源数据库平台上： SQL> col PLATFORM_NAME for a40 SQL>  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT

---------------------------------------- -------------- Linux x86 64-bit Little

--在目标数据库平台上：

SQL> col PLATFORM_NAME for a40 SQL>  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT

---------------------------------------- -------------- Linux x86 64-bit Little

由于源和目标平台的Endianness一致，可以省去convert这一步。

--查看 transportable_platform 支持总表 select * from v$transportable_platform; 4、检查源库表空间是否自包含 SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS',true); PL/SQL 过程已成功完成。 SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; 未选定行 说明表空间是自包含的。 SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTSIND',true); PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS

-------------------------------------------------------------------------------- ORA-39907: Index TTS.IND_DATAPUMP in tablespace TTSIND points to table TTS.DATAP UMP in tablespace TTS.

ORA-39907: Index TTS.IND_DATAPUMP02 in tablespace TTSIND points to table TTS.DAT

APUMP02 in tablespace TTS.

5、产生传输表空间集 （源库）

SQL>CREATE DIRECTORY dump_dir as '/home/oracle/dump_dir'; [oracle@11g ~]$ mkdir -p /home/oracle/dump_dir SQL>grant read,write on directory dump_dir to SYSTEM; 将表空间置为只读。 SQL> alter tablespace tts read only; 表空间已更改。 SQL> alter tablespace ttsind read only; 表空间已更改。 -- 检查表空间是否为read only SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';

TABLESPACE_NAME STATUS

------------------------------ --------- TTS READ ONLY TTSIND READ ONLY 使用数据泵导出传输表空间的元数据 [oracle@11g dump_dir]$ expdp \'sys/oracle as sysdba\' DIRECTORY=dump_dir DUMPFILE=tts_%U.dmp transport_tablespaces=tts,ttsind transport_full_check=y logfile=exp_tts.log parallel=1

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 11 14:20:02 2015 elapsed 0 00:00:59

注：如果Endianness不一致，还需要使用RMAN进行转换表空间的数据文件。

若两个平台字节序不相同，通过RMAN命令将源库中需要传输的表空间所对应的数据文件转换： >rman target / RMAN> convert tablespace ‘表空间名’ to platform=’目标平台‘ db_file_name_convert=’数据文件位置’,’转换后的位置’; 如：将HP-UX (64-bit) Big 转换成Linux IA (64-bit) Little RMAN> convert tablespace ‘test1’  to platform=’Linux IA (64-bit)’ db_file_name_convert=’/u01/oradata/test01.dbf’,’/temp/test01.dbf’;

6、目标库创建相关环境

SQL>CREATE DIRECTORY dump_dir as '/home/oracle/dump_dir'; [oracle@11g ~]$ mkdir -p /home/oracle/dump_dir SQL>grant read,write on directory dump_dir to SYSTEM;

7. 将Transportable Tablespace set 传送到Target端

--查看源库表空间数据文件路径

SQL> select name from v$datafile where ts# in (select ts# from v$tablespace where name in ('TTS','TTSIND'));

NAME

-------------------------------------------------------------------------------- /home/oracle/app/oradata/orcl/tts01.dbf /home/oracle/app/oradata/orcl/ttsind01.dbf （1）将表空间tts , ttsind 对应的数据文件copy到Target 对应的ORADATA目录下。 （2）将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下 [oracle@11g dump_dir]$ scp tts_01.dmp 10.100.25.13:/home/oracle/dump_dir/ oracle@10.100.25.14's password:  tts_01.dmp 100% 132KB 132.0KB/s 00:00 [oracle@11g orcl]$ scp tts01.dbf 10.100.25.13:/home/oracle/app/oradata/orcl/ oracle@10.100.25.14's password:  tts01.dbf 100% 40MB 40.0MB/s 00:01  [oracle@11g orcl]$ scp ttsind01.dbf 10.100.25.13:/home/oracle/app/oradata/orcl/ oracle@10.100.25.14's password:  ttsind01.dbf 100% 40MB 40.0MB/s 00:01

--将源库表空间改为 read writer

SQL> alter tablespace ttsind read write; Tablespace altered.

SQL> alter tablespace tts read write;

Tablespace altered.

8、导入表空间 （目标库）

-- 目标库检查是否拥有TTS表空间内所有用户 SQL> select username from dba_users where username in ('TTS'); --在目标数据库中，如果没有，创建相应的用户。 SQL> create user tts identified by tts; User created. SQL> grant connect,resource to tts; Grant succeeded.

--在Target 系统上Import 表空间的metadata。

[oracle@11g dump_dir]$ impdp \'sys/oracle as sysdba\' DIRECTORY=dump_dir DUMPFILE=tts_%U.dmp transport_datafiles=/home/oracle/app/oradata/orcl/tts01.dbf,/home/oracle/app/oradata/orcl/ttsind01.dbf parallel=1 logfile=exp_tts.log

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 11 18:18:33 2015 elapsed 0 00:00:14

注意：

（1）这里transport_datafiles 写的是datafile的全路径， 也可以用transport_datafiles来对数据文件进行重命名和移动位置。 （2）如果文件很多，也可以写入个配置文件里。 导入时通过PARFILE参数来指定。

--将被导入的表空间置为read write。

SQL> alter tablespace tts read write; Tablespace altered. SQL> alter tablespace ttsind read write; Tablespace altered.

9、核查对象

SQL> select segment_type,count(1) as"NUM" from dba_segments where tablespace_name in ('TTS','TTSIND') group by segment_type;

SEGMENT_TYPE NUM

------------------ ---------- TABLE 2 INDEX 2

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