OraFAQ Forum: SQL & PL/SQL » Merging all subpartitions in one partition

Home » SQL & PL/SQL » SQL & PL/SQL » Merging all subpartitions in one partition (Oracle 11.2.0.2.0)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Merging all subpartitions in one partition [message #571114]

Wed, 21 November 2012 03:48

ric90
Messages: 36
Registered: May 2011
Location: Belfort

Member

Hi everyone,

I'm looking for a way to delete my subpartitions by keeping my datas, and keep it into my partitions.
In fact, i want to remove my subpartitions, and keep my table partitionning.
I already remove my subpartition template, but i don't want to do an insert as select on a new table wich will be partitionned (without subpartitions).

ALTER TABLE myTable SET SUBPARTITION TEMPLATE ();

Tanks in advance for your advice

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571115 is a reply to message #571114]

Wed, 21 November 2012 03:49

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use DBMS_REDEFINITION.

Regards
Michel

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571116 is a reply to message #571115]

Wed, 21 November 2012 03:50

ric90
Messages: 36
Registered: May 2011
Location: Belfort

Member

Michel Cadot wrote on Wed, 21 November 2012 10:49

Use DBMS_REDEFINITION.

Regards
Michel

Thank you for your quick response ;)l
I'll check in oracle docs right now.

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571118 is a reply to message #571116]

Wed, 21 November 2012 04:34

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Note that in the background it does an insert select but it is (almost) transparent for you and your users but it nevertheless requires the double space for a time.

Regards
Michel

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571122 is a reply to message #571118]

Wed, 21 November 2012 05:56

ric90
Messages: 36
Registered: May 2011
Location: Belfort

Member

Michel Cadot wrote on Wed, 21 November 2012 11:34

Note that in the background it does an insert select but it is (almost) transparent for you and your users but it nevertheless requires the double space for a time.

Regards
Michel

Yep, that what i saw ... Sad

There is no way to remove subpartitionnig in another way ??
I have a lot of table (about 150) with a lot of datas... but accorrding to the oracle docs, i didn't find anything else ....

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571127 is a reply to message #571122]

Wed, 21 November 2012 06:25

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no other way than recreate the table.
You can merge all subpartitions until you only have one, depending on the your subpartition type, it may be possible.

Regards
Michel

Report message to a moderator

Re: Merging all subpartitions in one partition [message #571133 is a reply to message #571122]

Wed, 21 November 2012 06:48

ric90
Messages: 36
Registered: May 2011
Location: Belfort

Member

yes, shame ... thank you anyway.

Report message to a moderator

Previous Topic:	Creating a DWMQY DIMENSION using Analytic Workspace Manager
Next Topic:	Quey help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 05:34:25 CDT 2013

Total time taken to generate the page: 0.26484 seconds