Oracle传输表空间在数据仓库ETL中的应用

在数据仓库项目中，ETL无疑是最为繁琐，也是最为耗时和最不稳定的，如果数据源和目标同为oracle，且满足了一定的条件，则可以使用

在数据仓库项目中，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