Solved

add sub partition oracle

Posted on 2014-04-28
3
1,284 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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