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.
GouthamAnandAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Wasim Akram ShaikConnect With a Mentor Commented:
DBMS_REDEFINITION is the better option to do so..

create a new table of your requirement

copy table dependents and sync it with existing partitioned table,

using ALTER TABLE may not do the required task,as ALTER command will not effect the existing sub-partitions
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
I am not familiar with partitioning in detail, but according to the documentation, you can just set a new subpartition template via ALTER TABLE and it overrides the exsiting subpartition template:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1007904
0
 
GouthamAnandAuthor Commented:
Thank you.
0
All Courses

From novice to tech pro — start learning today.