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?

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

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