Home » SQL & PL/SQL » SQL & PL/SQL » Existing Partitions with maxvalue need new partitions
Existing Partitions with maxvalue need new partitions [message #297601] Fri, 01 February 2008 12:27 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I have a table that is currently partitioned with a high_value of '2008-01-01'. There is also a maxvalue partition to handle any data that exceeds the high_value. If I create two new partitions with high_values of '2008-02-01' & '2008-03-01', will the data allocated in the maxvalue partition be reassigned to these new partitions?

Thanks.
Re: Existing Partitions with maxvalue need new partitions [message #297603 is a reply to message #297601] Fri, 01 February 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Existing Partitions with maxvalue need new partitions [message #297604 is a reply to message #297603] Fri, 01 February 2008 12:37 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Great! Thanks!
Re: Existing Partitions with maxvalue need new partitions [message #297605 is a reply to message #297604] Fri, 01 February 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't create new partition (add partition), you split the maxvalue one.

Regards
Michel
Re: Existing Partitions with maxvalue need new partitions [message #297614 is a reply to message #297605] Fri, 01 February 2008 13:23 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Will the below statement automatically set the high_value for the new partition TAB_PART_P200801 to TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')?
alter table parttable split partition maxvalue at 
(TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 
into (PARTITION TABPART_P200801, PARTITION maxvalue)
update global indexes;

If I want to then create another partition for the month of February, would I issue the split with maxvalue again, but this time setting the split to occur at '2008-03-01'?

Thanks.

[Updated on: Fri, 01 February 2008 13:27] by Moderator

Report message to a moderator

Re: Existing Partitions with maxvalue need new partitions [message #297616 is a reply to message #297614] Fri, 01 February 2008 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and yes.

All is explained in documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2150203
The best is to make some small tests and see what happens.

Regards
Michel
Re: Existing Partitions with maxvalue need new partitions [message #297617 is a reply to message #297616] Fri, 01 February 2008 13:33 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Ok, great. Thanks very much.
Previous Topic: Passing Parameters into a Stored Procedure?
Next Topic: Lag function
Goto Forum:
  


Current Time: Fri Dec 09 21:33:03 CST 2016

Total time taken to generate the page: 0.23477 seconds