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

That should have read

    'two new segments'
    'the original segment'

--

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

Jonathan Lewis wrote in message
<954180285.20236.0.nnrp-04.9e984b29_at_news.demon.co.uk>...


>

>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

>

>