在数据仓库项目中，etl无疑是最为繁琐，也是最为耗时和最不稳定的，如果数据源和目标同为oracle，且满足了一定的条件，则可以使用oracle的传输表空间来帮助etl提高效率。
要想使用传输表空间，必须满足以下几个条件：
源与目标库都必须大于8i；
对于低于10g的版本，源与目标库必须为统一平台；
自包含：可以通过以下语句予以检测：
sys@racdb1 sql>exec dbms_tts.transport_set_check('ts_big1',true);
pl/sql procedure successfully completed.
sys@racdb1 sql>select * from transport_set_violations;
no rows selected
没有返回行，说明源表空间是自包含的，否则需要处理，另传输表空间不要包含sys的对象。
源表空间为read only
虽然从9i开始不需要源和目标的blocksize一样，但如果不一致,需要在目标数据库中增加相应的db_xk_cache_size,如本次实验中源数据库的blocksize为8k，目标数据库的blocksize为16k，则需要在目标库中增加db_8k_cache_size=8192参数，否则impdp时会报错ora-29339.
本实验中数据源为一个linux平台的oracle10g的分区表，目标为一个windows2008平台的oracle10g，实现步骤为：
1.确定源数据库的类型：
sys@racdb1 sql>select * from gv$version;
inst_id banner
---------- ----------------------------------------------------------------
1 oracle database 10g enterprise edition release 10.2.0.5.0 - 64bi
1 pl/sql release 10.2.0.5.0 - production
1 core 10.2.0.5.0 production
1 tns for linux: version 10.2.0.5.0 - production
1 nlsrtl version 10.2.0.5.0 - production
sys@racdb1 sql>select p.platform_name, p.endian_format
from v$transportable_platform p, v$database d
where p.platform_name = d.platform_name;
platform_name endian_format
---------------------------------------- --------------
linux x86 64-bit little
2.确定目标数据库的类型：
cczdba@bidb sql>select * from v$version;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production
pl/sql release 11.2.0.1.0 - production
core 11.2.0.1.0 production
tns for 64-bit windows: version 11.2.0.1.0 - production
nlsrtl version 11.2.0.1.0 - production
cczdba@bidb sql>select p.platform_name, p.endian_format
2 from v$transportable_platform p, v$database d
3 where p.platform_name = d.platform_name;
platform_name endian_format
-------------------------------------------- ----------------------------
microsoft windows x86 64-bit little
3.在源库中创建各个分区具有独立表空间的分区表：
cczdba@racdb1 sql>create tablespace ts_big1 datafile '+racdat' size 100m autoextend on uniform size 10m;
tablespace created.
cczdba@racdb1 sql>create tablespace ts_big2 datafile '+racdat' size 100m autoextend on uniform size 10m;
tablespace created.
sys@racdb1 sql>create table scott.bigtab
2 (
3 ins_time date,
4 owner varchar2(30 byte),
5 object_name varchar2(128 byte),
6 subobject_name varchar2(30 byte),
7 object_id number,
8 data_object_id number,
9 object_type varchar2(19 byte),
10 created date,
11 last_ddl_time date,
12 timestamp varchar2(19 byte),
13 status varchar2(7 byte),
14 temporary varchar2(1 byte),
15 generated varchar2(1 byte),
16 secondary varchar2(1 byte)
17 )
18 partition by range (ins_time)
19 (
20 partition ins_20120416 values less than (to_date(' 2012-04-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
21 logging
22 nocompress
23 tablespace ts_big1,
24 partition ins_20120417 values less than (to_date(' 2012-04-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
25 logging
26 nocompress
27 tablespace ts_big2
28 );
table created.
sys@racdb1 sql>conn scott/tiger
connected.
scott@racdb1 sql>insert into bigtab select sysdate-1,a.* from dba_objects a;
50286 rows created.
scott@racdb1 sql>commit;
commit complete.
scott@racdb1 sql>insert into bigtab select sysdate,a.* from dba_objects a;
50286 rows created.
4.建立临时表以和分区ins_20120416进行交换，一满足表空间ts_big1为自包含：
注意在交换之前该分区所在的表空间不满足自包含的要求，无法导出：
sys@racdb1 sql>exec dbms_tts.transport_set_check('ts_big1',true);
pl/sql procedure successfully completed.
sys@racdb1 sql>select * from transport_set_violations;
violations
--------------------------------------------------------------------------------
default partition (table) tablespace users for bigtab not contained in transport
able set
partitioned table scott.bigtab is partially contained in the transportable set:
check table partitions by querying sys.dba_tab_partitions
[oracle@linux1]expdp cczdba/cczdba dumpfile=trans_ts.dmp directory=data_pump_dir transport_tablespaces=ts_big1
export: release 10.2.0.5.0 - 64bit production on tuesday, 17 april, 2012 13:20:02
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, real application clusters, olap, data mining
and real application testing options
starting cczdba.sys_export_transportable_01: cczdba/******** dumpfile=trans_ts.dmp directory=data_pump_dir transport_tablespaces=ts_big1
ora-39123: data pump transportable tablespace job aborted
ora-29341: the transportable set is not self-contained
job cczdba.sys_export_transportable_01 stopped due to fatal error at 13:20:12
交换后：
scott@racdb1 sql>create table bigtab_temp as select * from bigtab where 1=2;
table created.
scott@racdb1 sql>alter table bigtab exchange partition ins_20120416 with table bigtab_temp;
table altered.
scott@racdb1 sql>conn /as sysdba
connected.
sys@racdb1 sql>exec dbms_tts.transport_set_check('ts_big1',true);
pl/sql procedure successfully completed.
sys@racdb1 sql>select * from transport_set_violations;
no rows selected
，