GouthamAnand
asked on
add sub partition oracle
Hi,
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
(PERIOD_CD VARCHAR2(100),
ATTRI_NAME varchar(100)
)
PARTITION BY LIST (PERIOD_CD)
SUBPARTITION BY LIST (ATTRI_NAME)
SUBPARTITION TEMPLATE
(
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?
Thanks.
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
(PERIOD_CD VARCHAR2(100),
ATTRI_NAME varchar(100)
)
PARTITION BY LIST (PERIOD_CD)
SUBPARTITION BY LIST (ATTRI_NAME)
SUBPARTITION TEMPLATE
(
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?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER