add sub partition oracle
Posted on 2014-04-28
I have a LIST-LIST partitioned oracle table. The table is not having the primary key.
It has default partition at the partition level and also at the sub partition level.
Now at the sub partition level I want to split the default partition and add one more list sub partition under the all the existing partitions.
I know that if primary key is existing then we can do with DBMS_REDEFINITION package.
But is there any way to add one more list sub partition?
I have basic table creation script as
CREATE TABLE TEST_TB
PARTITION BY LIST (PERIOD_CD)
SUBPARTITION BY LIST (ATTRI_NAME)
SUBPARTITION PT_01 VALUES ('ABC'),
SUBPARTITION PT_02 VALUES ('DEF'),
SUBPARTITION PT_03 VALUES ('GHI'),
SUBPARTITION PT_DEFAULT VALUES (DEFAULT)
( PARTITION DATA_53 VALUES ('53'),
PARTITION DATA_54 VALUES ('54'),
PARTITION DATA_55 VALUES ('55'),
PARTITION DATA_DEFAULT VALUES (DEFAULT)
Now I want to add one more sub partition SUBPARTITION PT_04 VALUES ('JKL') by splitting the default subpartition. which should reflect in all partitions.
Can you please suggest?