Home » SQL & PL/SQL » SQL & PL/SQL » Partitions (oracle 12c)
Partitions [message #658929] Sun, 01 January 2017 06:04 Go to next message
bheemasql
Messages: 1
Registered: January 2017
Junior Member
We have history_details table it contains (365*3) 1095 Range partitions (from 01-01-2016 to 31-12-2016) and Maxvalue partition.


i need to split max partition as (365*3) 1095 Range partitions (from 01-01-2017 to 31-12-2017).

i am following ALTER TABLE HISTORY_DETAILS PARTITION DND_MAXPART AT(.....

in this approach it will take each partition 8mb tablespace (it is taking empty rows partition also).

Note: maxpartition having large no of data.

can u tell me any solution for reclaim usage
Re: Partitions [message #658930 is a reply to message #658929] Sun, 01 January 2017 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Reclaim space for a couple of GB? When you know the price of EE and "partitioning" options and the price of disks, it seems quite silly to optimize space at this level.

Anyway, the best way for you should be to go to INTERVAL partitioning and no more care about all this.

Re: Partitions [message #658931 is a reply to message #658930] Sun, 01 January 2017 07:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2711
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Sun, 01 January 2017 07:25

Anyway, the best way for you should be to go to INTERVAL partitioning and no more care about all this.
As long as "Maxvalue partition" is empty and can be dropped. Otherwise it will be a bit more complex.

SY.
Re: Partitions [message #658953 is a reply to message #658929] Mon, 02 January 2017 01:44 Go to previous messageGo to next message
flyboy
Messages: 1851
Registered: November 2006
Senior Member
I wonder how others were able to find out your demand as I cannot deduce it from your post.

Firstly, from 11.2.0.2, the empty partitions may not occupy any physical space.
http://docs.oracle.com/database/122/SQLRF/CREATE-TABLE.htm#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__CEGBDDBC
Did you really check the space occupied by those "each partition 8mb tablespace"? How?
Are you really using Oracle version 12c? Which exact one?
Was the table created with explicit SEGMENT CREATION IMMEDIATE?
Does the table hit another its restrictions (e.g. is it in SYSTEM tablespace)?

Then, you write about "maxpartition having large no of data".
And, as the last sentence is evidently cut in the middle, it is not clear to me, "reclaim usage" of what you want/expect - those empty(?) new partitons or the partition for MAXVALUE?
Or is it referring to something else than the space occupied by partitions?
Re: Partitions [message #658998 is a reply to message #658953] Tue, 03 January 2017 09:49 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Why are you manually creating partitions? You can easily have the database automatically create them for you. A link for how to do it follows

http://www.dba-oracle.com/t_interval_partitioning.htm
Previous Topic: Insert the data into dynamically created table (2 threads merged by bb)
Next Topic: SYSDATE - 1 'MM' for JAN showing 0 instead of 12
Goto Forum:
  


Current Time: Sun Feb 25 16:27:51 CST 2018

Total time taken to generate the page: 0.08442 seconds