0

I have a table which is partitioned by list and each partition have multiple values. For example:

partition by list (COL1)
(
  partition GROUP_1 values ('VAL1','VAL2','VAL3')
)

This table has huge data and sometimes, multiple sessions are trying to access same block and I am facing read by other session problem. So, I am thinking of changing the structure to below:

Alter Table Table_1
Split Partition GROUP_1 INTO
  partition GROUP_2 values ('VAL1'),
  partition GROUP_3 values ('VAL2'),
  partition GROUP_4 values ('VAL3')
)  PARALLEL 5;

I cannot use DBMS_REDEF as it will cause object invalids. I am trying to use Split Partitions but I am getting ORA-14028.

What would be the best way of achieving this?

Thanks a lot.

user1140840
  • 79
  • 10

1 Answers1

0

I found a recursive way of achieving this. 

Alter Table Table_1
Split Partition GROUP_1 VALUES ('VAL1') INTO
  (partition GROUP_1,
  partition GROUP_2)  PARALLEL 5;

Then 

Alter Table Table_1
Split Partition GROUP_2 VALUES ('VAL2') INTO
  (partition GROUP_2,
  partition GROUP_3)  PARALLEL 5;

This way, we create 3 different partition for 3 different value.

Is there any other faster way of doing this? Also, do you see any problems related to index or tablespace at all in the approach above? Thanks

user1140840
  • 79
  • 10