Re: Q about extent allocation and free space management with partitions

If you split a partition, Oracle creates 2 new extents and copies the data
into them. The original extent stays
in existence until the split is complete, and is then freed.

Yes, is you drop the partition, Oracle
can reclaim the space in just the same
way as it would if you dropped a table;
a (range or hash) partition is just another type of data segment (a composite partition is a collection of hash subpartitions - so if you drop a single range from a composite partition you drop several data segments).

You might be better off anyway trying to keep the top end partitions set to high
values which mean you just keep adding
new partitions ahead of time rather than splitting the top one

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Greg Stark wrote in message
<87og81hb5d.fsf_at_HSE-Montreal-ppp32127.qc.sympatico.ca>...


>

>What happens if I have a 1G table spread over 10 100M extents and I split

 the


>partition in two? Does Oracle have to allocate 5 new 100M extents and the

 main


>partition now has 10 extents half of which are empty?

>

>What happens if I drop the first partition? is Oracle capable of recovering

>the 5 extents as free space?

>

>I'm asking because we're establishing purging policies and considering how

 to


>implement them. What appears to be the most natural way is to partition the

>table by day (nearly all transactions use records from within the same day)

>and keep a certain number of older partitions. Each day the lead partition

>would be split and the oldest partition would be exchanged out, exported,

 and


>dropped.

>

>I'm just worried that while this seems like a nice procedure I'm worried

 that


>the lead partition will never recover the space being freed off the

 beginning


>by the splits. If it just keeps marching on is there any chance it will

>consume more and more space each day just as if we weren't purging?

>

>

>--

>greg