Solved

add sub partition oracle

Posted on 2014-04-28
3
1,320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

617 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