-
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:
alter table 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:
alter table pctfree 0;
Do I need a move command within the same TS?
rebuild global indexes?
Thanks
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by BeefStu
When I usually re-claim space in a paritioned table I do the following:
alter table table name move partition partition name compress
storage( initial 1k next 1k pctincrease 0 minextents 1 )
1K initial and next?
Usually, partitioned tables are - by design - large tables; having extent size of 1K would create zillions of extents.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
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.
Paul,
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
missing something.
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".
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|