Solved

add sub partition oracle

Posted on 2014-04-28
3
1,253 Views
Last Modified: 2014-05-02
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.
0
Comment
Question by:GouthamAnand
3 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 200 total points
ID: 40028492
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
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 300 total points
ID: 40028832
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
 

Author Closing Comment

by:GouthamAnand
ID: 40036944
Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question