Oracle傳輸資料表空間在資料倉儲ETL中的應用

在資料倉儲項目中，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

1 2 下一頁