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 - ProductionSYS@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 Little2.确定目标数据库的类型：
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 - ProductionCCZDBA@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 Little3.在源库中创建各个分区具有独立表空间的分区表：
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 setPartitioned 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_big1Export: Release 10.2.0.5.0 - 64bit Production on Tuesday, 17 April, 2012 13:20:02Copyright (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-containedJob "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