Home » RDBMS Server » Server Administration » Create List Subpartition for an existing List partition (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
Create List Subpartition for an existing List partition [message #572636] Fri, 14 December 2012 05:32 Go to next message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
Dear All,

We have a table which is already partitioned by list and now we would like to add a subpartition.

Create table Item_mst
(
ccn varchar2(10),
Flag varchar2(1),
Item varchar2(10),
status varchar2(1)
)
partition by list(CCN)
(
partition Item_mst_Test values('ABC'),
partition Item_mst_Test1 values('DEF')
);

Now I could like to alter the above table for adding a new subpartition on each partition on status.

ALTER TABLE Item_mst
MODIFY PARTITION Item_mst_Test
ADD SUBPARTITION Item_mst_Test_A VALUES ('A');

Above alter gives ORA-14253: table is not partitioned by composite range method.

However dropping and recreating the table with subpartitions is working fine.

But Dropping and recreating the table in production is very cumbersome as it has huge data and many indices.

Please let me know if you have any suggestions.

Thanks
Re: Create List Subpartition for an existing List partition [message #572639 is a reply to message #572636] Fri, 14 December 2012 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_REDEFINITION

Regards
Michel
Re: Create List Subpartition for an existing List partition [message #572728 is a reply to message #572639] Mon, 17 December 2012 00:31 Go to previous messageGo to next message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
Thanks Michel for the reply.

Do you mean there is no way to alter the existing table for creating a new List subpartition instead of creating a New table with necessary partitions and subpartitions and using DBMS_REDEFINITION to apply the changes..
Re: Create List Subpartition for an existing List partition [message #572733 is a reply to message #572728] Mon, 17 December 2012 01:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, there is no way.
You have to realize that a non-partitioned table, a partitioned non-subpartitioned table and a subpartition table have totally different physical structures and so a simple ALTER couldn't do it.

Regards
Michel
Previous Topic: Oracle Database Shutdown Failed.
Next Topic: Command to open a alert log
Goto Forum:
  


Current Time: Fri Mar 29 04:53:15 CDT 2024