在数据仓库项目中,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平台的oracle11g,实现步骤为:
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
 
 
5.设置表空间为只读属性并导出:
SYS@racdb1 SQL>alter tablespace ts_big1 read only;
 
[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 14:01:00
 
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
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "CCZDBA"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CCZDBA.SYS_EXPORT_TRANSPORTABLE_01 is:
 /u01/app/oracle/product/10.2.0/db_1/rdbms/log/trans_ts.dmp
Job "CCZDBA"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:01:41
 
 
6.将分区交换回去并修改回可写:
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>alter tablespace ts_big1 read write;
 
Tablespace altered.
 
 
7.用rman做跨平台文件格式转换:
RMAN> convert tablespace ts_big1 to platform 'Microsoft Windows x86 64-bit' format '/tmp/%U';
 
Starting backup at 17-APR-2012 14:26:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 instance=racdb1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+RACDAT/racdb/datafile/ts_big1.269.780845025
converted datafile=/tmp/data_D-RACDB_I-772885600_TS-TS_BIG1_FNO-6_3in8ljku
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished backup at 17-APR-2012 14:26:53
 
 
8.将上述expdp导出的metadata和转化的数据文件用ftp的bin方式传送目标server中,注意tbs_exp.dmp要放在对应data_pump_dir目录中,而数据文件则放在目标数据库的数据文件所在的目录:
C:\oracle\oradata\BIDB>impdp cczdba/cczdba dumpfile=tbs_exp.dmp directory=data_p
ump_dir transport_datafiles=C:\oracle\oradata\BIDB\tbs_big1.dbf remap_schema=(sc
ott:scott)
 
Import: Release 11.2.0.1.0 - Production on Tue Apr 17 16:36:36 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CCZDBA"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 
Starting "CCZDBA"."SYS_IMPORT_TRANSPORTABLE_01": cczdba/******** dumpfile=tbs_e
xp.dmp directory=data_pump_dir transport_datafiles=C:\oracle\oradata\BIDB\tbs_bi
g1.dbf remap_schema=(scott:scott)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "CCZDBA"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:36:58
 
 
9.检验目标库中导入的数据表,注意由于在源数据库中做了分区交换,因此导入的表名称为bigtab_temp
SYS@BIDB SQL>conn scott/tiger
Connected.
SCOTT@BIDB SQL>select count(*) from bigtab_temp;
 COUNT(*)
----------
     50286
 
 
10.   至此数据已经成功的传送到了目标数据库中,可以根据需要将该表交换到相应的分区;
SCOTT@BIDB SQL>alter table bigtab exchange partition INS_20120416 with table bigtab_temp;
Table altered.