Tablespace size refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Tablespace size refresh

  1. #1
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49

    Tablespace size refresh

    Hi,

    Anyone knows how to make the tablespace size from the dba_free_space view, refresh to reflect the actual usage after housekeeping?

    e.g initial tablespace size free space is 10%. After i did some housekeep and delete hundred thousands of record, the free space size remains the same. If i'm not wrong, extents were allocated to a particular table, even if i delete many records, the extents usage will still be there. Dropping a table definatley will see a new free space value for a tablespace.

    If there any way i could delete all those unused extents?
    Regards
    CF

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    When extents are allocated to a table and you delete records from that table..the allocated extents would not be freed...the blocs of these extents would be free and the space in them would be used for subsequent inserts...

    If you want to refresh your dba_free_space and you are on 8i then you could simply

    1)Create a new locally managed tablespace
    2)Alter table table_name move to new locally managed tablespace
    3)Rebuild the indexes on that table..


    You could also try alter table my_table deallocate unused..


    The important thing here is the definition of "unused" - by this Oracle means never used space, that is, anything above the table's current high water mark. Unfortunately, in a table of 'n' blocks where 'm' are used (n>m), the distribution of those 'm' blocks is (as good as) random. So (if you're lucky) you might find that your large table was simply an error of initial allocation, and that much of the table has never been used. In this case, altering the table to deallocate unused space is an "instantaneous" way to reclaim the space.

    if you are in version less than 8i then you need to unload and reload the table...

    You also read about high water marks ..

    http://www.samoratech.com/OraAdmin/swHighWaterMark.doc

    regards
    Hrishy
    Last edited by hrishy; 11-21-2002 at 12:35 AM.

  3. #3
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49
    Thanks hrishy,

    I guess some of my tables have extents all over the place. That's why even after deallocate unused. The tbs size still remains the same.
    Once, again, thanks for your advise
    Regards
    CF

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