+ Reply to Thread
Results 1 to 6 of 6
  1. 01-25-2011 07:10 PM #1
    BeefStu
    BeefStu is offline Member
    Join Date
    Jul 2006
    Posts
    195

    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
    Reply With Quote Reply With Quote
  2. 01-26-2011 09:45 AM #2
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,485
    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.
    Reply With Quote Reply With Quote
  3. 01-26-2011 09:56 AM #3
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,485
    Quote Originally Posted by BeefStu View Post
    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.
    Reply With Quote Reply With Quote
  4. 01-26-2011 10:52 AM #4
    BeefStu
    BeefStu is offline Member
    Join Date
    Jul 2006
    Posts
    195
    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.
    Reply With Quote Reply With Quote
  5. 01-27-2011 04:26 PM #5
    BeefStu
    BeefStu is offline Member
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by PAVB View Post
    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
    Reply With Quote Reply With Quote
  6. 01-27-2011 04:54 PM #6
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,485
    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.
    Reply With Quote Reply With Quote
    7. « Previous Thread | Next Thread »

    Bookmarks

    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