Application of Oracle tablespace in data warehouse ETL

In the data warehouse project, ETL is undoubtedly the most tedious, time-consuming, and unstable. If the data source and target are both Oracle and meet certain conditions, you can use the oracle tablespace to improve ETL efficiency.
To use a tablespace, the following conditions must be met:
The source and target databases must both be larger than 8i;
Ø for versions earlier than 10 Gb, the source and target databases must be a unified platform;
Ø self-contained: You can use the following statements to check the self-contained items:
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
If no row is returned, it indicates that the source tablespace is self-contained. Otherwise, it must be processed. In addition, the destination tablespace cannot contain sys objects.
The source tablespace is read only.
Although the source and target blocksize are not the same from 9i, if they are inconsistent, add the corresponding db_xk_cache_size to the target database. For example, in this experiment, the source database blocksize is 8 k, the blocksize of the target database is 16 k, you need to add the db_8k_cache_size = 8192 parameter in the target database, otherwise impdp will report an error ORA-29339.
 
In this experiment, the data source is an oracle10g partition table on a linux platform, and the destination is oracle10g on windows2008. The implementation steps are as follows:
1. determine the source database type:
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. Determine the target database type:
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. Create a partition table with independent tablespace for each partition in the source database:
CCZDBA @ racdb1 SQL> create tablespace ts_big1 datafile '+ RACDAT 'size 100 M autoextend on uniform size 10 m;
Tablespace created.
CCZDBA @ racdb1 SQL> create tablespace ts_big2 datafile '+ RACDAT 'size 100 M autoextend on uniform size 10 m;
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 ('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 ('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;
50286 rows created.
SCOTT @ racdb1 SQL> commit;
Commit complete.
SCOTT @ racdb1 SQL> insert into bigtab select sysdate, a. * from dba_objects;
 
50286 rows created.
 
 
4. Create a temporary table and exchange it with the partition INS_20120416. A tablespace ts_big1 must be self-contained:
Note that the tablespace in the partition does not meet the self-contained requirements before the swap, and cannot be exported:
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 minutes l, 2012 13:20:02
 
Copyright (c) 2003,200 7, 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
The ORA-39123: Data Pump transportable tablespace job aborted
The ORA-29341: The transportable set is not self-contained
 
Job "CCZDBA". "SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 13:20:12
After exchange:
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 Next Page