shrinking a partitioned table (pctfree)
I am looking for the best way to reclaim unused space for my paritioned table.
My predessor setup a table with a pctfree of 40,which is a huge waste of
space, since all of the data that goes into this table is never updated.
Therefore, I am thinking I would like to set pctfree 0 to see how much
space I can re-gain.
When I usually re-claim space in a paritioned table I do the following:
move partition compress
storage( initial 1k next 1k pctincrease 0 minextents 1 );
Can somebody please provide me with the high level steps (correct
syntax) to decrease my pctfree from 40 to 0 on a partitioned table.
Would it be this:
Do I need a move command within the same TS?
rebuild global indexes?
I woud do alter table move partition pctfree (I don't like zero) update global indexes.
You have to rebuild locally partitioned indexes
Wether you move it into same tablespace or other tablespace depends on your particular scenario - available space and size of the target partition.
1K initial and next?
Originally Posted by BeefStu
Usually, partitioned tables are - by design - large tables; having extent size of 1K would create zillions of extents.
the 1k and 1k was just filler in the email to complete my syntax. Apologies
for that as I was not logged into my DB when I wrote the email.
Thanks for the response.
Originally Posted by PAVB
can you expand a bit more on your explanation. My goal is to recover space
but when I change my pctfree my current partitions do not appear to shrink
nor would I expect them too. Would this change only help me going forward
when new partitions are created.
I looking to accomplish two things:
First, de-crease the pctfree since this table is never being updated.
Second, regain as much space for each partiton.
Can you provide some syntax that would accomplish both or am I
Thanks in advance for your help
What's your PCTUSED?
Have you set it up when you lowered PCTFREE?
Have you reorg the table partition by doing alter table move partition?
If PCTFREE is altered on-the-fly allocated blocks are not affected at least you run DBMS_REPAIR.SEGMENT_FIX_STATUS for the affected segment.
The easiest way to reclaim space is to reorg table partitions setting new pctfree/pctused during reorg a.k.a. "alter table move partition".