ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

11g Oracle Data Integrator – Part 8/11g – Table Partitioning supported

Hi everyone…

Some new features on Oracle Data Integrator 11g. A lot of  “Oracle people” will enjoy it even that still needs some improvements…

We have seen that in ODI 11g the support for table partition . We wanted to see the ability and support and recognition of it , so we have done some sample run through test and here we are sharing what we have seen .

Here we are creating a Partition based table.

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER(6,0),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0) )
PARTITION BY RANGE (EMPLOYEE_ID)
( PARTITION P1 VALUES LESS THAN (120) TABLESPACE USERS ,
PARTITION P2 VALUES LESS THAN (140) TABLESPACE USERS,
PARTITION P3 VALUES LESS THAN (160) TABLESPACE USERS,
PARTITION P4 VALUES LESS THAN (200) TABLESPACE USERS ,
PARTITION P5 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

After running the script in database , We have used the selective reverse and have found that it failed to recognizing the partition, so we reran the reverse using the RKM Oracle.

clip_image002

The Oracle RKM successfully recognized and captured the Partition and was able to see in the Datastore also as a standard practice its always great to go for Oracle RKM.

clip_image004

Since database handles the partition allocation, handling the insert was easy task.

clip_image006

SELECT * FROM EMPLOYEES PARTITION (P1);

clip_image008

Now creating a temporary interface with partition on it and loading the data

we also wanted to check if the Create table can create a partition based table and we have created database structure with the required partition type and values and used the same into the target.

clip_image016

clip_image018

clip_image020

clip_image022

clip_image024

As you can see the Create table was created without Partition, so still the KM don’t have the support to handle the partition and would need to be created via either database or Generate DDL and run the same in the target.

clip_image030

clip_image026

 Well, as I told at the beginning still needs some improvements but already useful.

Thank you for visit us!!!!

Leave a Reply

Required fields are marked *.