Getting Back Space Reserved w/ PCT_FREE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Getting Back Space Reserved w/ PCT_FREE

  1. #1
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    A bunch of our fact tables were created w/ PCT_FREE = 10.
    The rows in these tables never get updated. We have lost a TON of space (200MB in a 2GB * 160 files) and I would like to reset PCT_FREE to 0. Is there a way to reclaim the space and reset the value w/o having to do a full export and reimport?

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    Hi Joe,

    When you change the PCTFREE parameter for a table the new settings apply
    to all data blocks used by the table. However blocks already allocated are not
    immediately reorganised but as necessary after change. If you are not making
    changes to the table or require the space to be reclaimed immediately then
    I believe an export/import is one option. Another way (which may be quicker)
    but would require double the disk space is:

    1. Create a copy of the table using
    'Create table copytable as (select * from origtable)'

    2. Drop the original table.

    3. Create a table (using the original table name) with the correct
    storage parameters.

    4. Insert the rows from the copy table.

    Hope this helps

    Moff

  3. #3
    Join Date
    Mar 2000
    Location
    india
    Posts
    54
    Hi Moff,
    You can avoid first 2 steps.(1,2) in the 4 th step instead of from copy table ,pick the rows from original table.

  4. #4
    Join Date
    Feb 2000
    Posts
    175
    Can you create a table with the same name in the same schema?

    If you

    create table origtable as (select * from origtable);

    you get an ORA-955 error - name already used by an existing object

    I'm assuming the original requirement was for the reorgainised table
    to have the same name and be in the same schema.

    Rgds

    Moff.

  5. #5
    Join Date
    Sep 2000
    Posts
    14
    Just RENAME the original table then you can reCreate and Insert.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width