Solved

add sub partition oracle

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 47
oracle 11g 23 84
Can't Access My Database 57 67
upgrading Oracle 10g/ 11g / 11g R2 to Oracle 12c 25 54
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

810 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