[ODI 11g] Table Partitioning

 

Table Partitioning

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.

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.

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

SELECT * FROM EMPLOYEES PARTITION (P1);

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.

 

 

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.