Home » SQL & PL/SQL » SQL & PL/SQL » Droping subpartitions of a partition
Droping subpartitions of a partition [message #141397] Mon, 10 October 2005 00:56 Go to next message
sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar
Member
Hi,
what is the query to drop all the sub partitions of a given partition?

Regds,
Srikanth
Re: Droping subpartitions of a partition [message #264892 is a reply to message #141397] Tue, 04 September 2007 23:14 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member


you can achive the goal by marge the subpartitions into one

ALTER TABLE <table_name>
MERGE SUBPARTITIONS <subpartition_name>
INTO SUBPARTITION <subpartition_name
TABLESPACE <tablespace_name>;
Re: Droping subpartitions of a partition [message #264923 is a reply to message #264892] Wed, 05 September 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if you can merge all subspartitions into one subpartition you still don't drop all subpartitions as one is kept.
There is no other way to drop all subpartitions than to drop the partition itself.

Now the way to do it depends if you have data you want to keep or not.
If there are no data just drop and recreate the partition.
If you have data, you can merge into a single subpartition, exchange the subpartition with a table, drop and recreate the partition and exchange the partition with the table.

Regards
Michel
Re: Droping subpartitions of a partition [message #264978 is a reply to message #264923] Wed, 05 September 2007 03:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Someone will correct me if I'm wrong, because I haven't used sub-partitions that much...

The only thing you can do with the sub-partitions of a GIVEN partition is to truncate or exchange them. You can only drop, merge, split or add sub-partitions on EVERY partition at once.

The reason for this - assuming still that my understanding is correct - is that every partition must have the same profile of subpartitions. ie. One partition cannot have 6 sub-partitions whilst another partition has 5 sub-partitions. Droppin, merging, splitting, and adding sub-partitions to a GIVEN partition would violate this rule.

Based of a brief glance at the 11g doco, this rule is unchanged even with all of the additional partitioning features.

Ross Leishman
Re: Droping subpartitions of a partition [message #264998 is a reply to message #264978] Wed, 05 September 2007 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (c1 integer, c2 integer, c3 integer)
  2  partition by range (c1)
  3  subpartition by list (c2)
  4  ( partition p1 values less than (100)
  5    ( subpartition sp11 values (0,1,2),
  6      subpartition sp12 values (3,4,5),
  7      subpartition sp13 values (default) ),
  8    partition p2 values less than (200)
  9    ( subpartition sp21 values (0,10,20),
 10      subpartition sp22 values (30,40,50),
 11      subpartition sp23 values (default) ),
 12    partition p3 values less than (maxvalue)
 13  )
 14  /

Table created.

SQL> alter table t merge subpartitions sp11, sp12 into subpartition sp11;

Table altered.

Regards
Michel
Re: Droping subpartitions of a partition [message #265240 is a reply to message #264998] Wed, 05 September 2007 21:40 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Huh! Well there you go.
Previous Topic: use CLOB or BLOD for storing html generated text
Next Topic: Create directory...
Goto Forum:
  


Current Time: Thu Dec 08 10:19:59 CST 2016

Total time taken to generate the page: 0.11274 seconds