Re: Table partitioning Oracle 9.2

Hi!

No, data blocks below partitions high-high water mark can never be used for another segment.
Unformatted blocks above (high)-high water mark can be used for another segment only when you trim the extent(s) using alter table deallocate unused (but this feature is useful only if you have lots of unformatted space in your segment for some reason).

So, if your business allow this table to be offline for a while, I'd recommend the following approach after you've archived your old data (some features here require 9i and syntax is written from memory, might have small errors in it):

1. archive your unneeded data
2. create table temp as select * from your_partitioned_tab partition P_1 where <condition to filter out old unneeded data>; (you can use nologging & parallel here if you want)
3. alter table your_partitioned_tab truncate partition P_1 drop storage update global indexes; (can use parallel here as well)
   • this will truncate your old partition and release any extents above it's minextents)
4. alter table your_partitioned_tab exchange partition P_1 with table TEMP excluding indexes without validation; (just exchanges the TEMP table's segment storing only the required 5% of data with old, now truncated segment).
5. drop table TEMP; (the 5% of required data was "moved" back to your_partitioned_tab in step 4.
6. rebuild any local indexes on P_1 partition.
7. analyze partition P_1 and it's local indexes, possibly global indexes on your table as well (btw, you can use "compute statistics" option for gathering basic statistice when rebuilding indexes...)

You can use nologging in evey operation mentioned above (with the exception of "update global indexes" in step 3). Of course in case of nologging operations, your backup strategy has to be "aware" of them.

So, instead of generating lots of redo and undo+redo due huge deletes and index maintenance you just take this small amount of rows you need, insert them into a new segment using direct path and nologging (very little undo and redo), and then just exchange the segments between your "old" and "new" tables.

Cheers,
Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, October 31, 2003 10:24 AM

> --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426
> Content-Type: text/plain; charset=us-ascii
>
>
>
>
> RDBMS Version: 9.2.0.1.0
> Operating System and Version: Solaris 8
> Error Number (if applicable):
> Product (i.e. SQL*Loader, Import, etc.): Partitioned Table
> Product Version: 9.2.0.1.0
>
> Table partitioning
>
> Hi,
>
> I've a query reg. space usage in context of partitioned tables.
>
> I've a table with 12 partitions P_1 ... P_12. Until now data
> got populated in P_1 upto P_6 and future data will come in P_7 etc.
> If i delete some huge amount of data from P_1 (after archiving it)
> will that freed space be used by future inserts (which happens in
subsequent partitions like P_7 etc).
> Unfortunately, we can't delete all data in partition. We have to keep some
data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts?
> Data is partitioned by date. So, my query is whether Oracle will put
future data (which belongs to partition P_7 etc.) in space earlier used by P_1.
>
> Any help from members is appreciated.
>
> Thanks,
> Vikas
>
> Get Your Private, Free E-mail from Indiatimes at
http://email.indiatimes.com
> Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com
> Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to
http://airsahara.indiatimes.com and Bid Now !
>
> --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426
> Content-Type: text/html; charset=us-ascii
>
> <P><STRONG></STRONG>&nbsp;</P>
> <P><STRONG>RDBMS Version</STRONG>: 9.2.0.1.0<BR><B>Operating System and
Version</B>: Solaris 8<BR><B>Error Number (if applicable)</B>: <BR><B>Product (i.e. SQL*Loader, Import, etc.)</B>: Partitioned Table<BR><B>Product Version</B>: 9.2.0.1.0<BR><BR>Table partitioning<BR><BR>Hi, <BR><BR>I've a query reg. space usage in context of partitioned tables. <BR><BR>I've a table with 12 partitions P_1 ... P_12. Until now data <BR>got populated in P_1 upto P_6 and future data will come in P_7 etc. <BR>If i delete some huge amount of data from P_1 (after archiving it) <BR>will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). <BR>Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? <BR>Data is partitioned by date. So, my query is whether Oracle will put fu!
> tu!
> re data (which belongs to partition P_7 etc.) in space earlier used by
P_1.<BR><BR>Any help from members is appreciated. <BR><BR>Thanks, <BR>Vikas <BR></P>
> <hr><font face="Arial" size="2"><b>Get Your Private, Free E-mail from
Indiatimes at </font><a href="http://email.indiatimes.com"><font face="Arial" size="2">http://email.indiatimes.com</a></b><br>Buy The Best In <b>BOOKS</b> at <A
href="http://www.bestsellers.indiatimes.com">http://www.bestsellers.indiatim es.com</A><br>Bid for <b>Air Tickets</b> on Air Sahara Flights. For Best Deals, log on to <a
href="http://airsahara.indiatimes.com">http://airsahara.indiatimes.com</a> and Bid Now !</font>
>
> --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426--
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Vikas S
> INET: vikas_soolapani_at_indiatimes.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).