Desktop

Ask something
Discussion Locked

Management of tablespace

0 helpful
2 replies

by mnpunde 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
This is a very typical problem encounterd while managing Oracle database. One important tablespace has grown to the size of 240 GB and it is not possible to export that table to any other disc because of paucity of hard discs.We want to run a critical process which is timebound and which adds data to the abovementioned tablespace.Since export is imediately not possible some data was removed because of which table has become fragmented. nearly 50 gb data is deleted and tablespace coalsced still the space occupied is shown as 260GB. What can be done so as to retrive space from this table immediately .

2 replies

by herbnet

The problem you could be encountering is caused by indexes. If the data is deleted from the table and the table has indexes with items like date or sequential values, the btree has become unbalanced and needs to be rebuilt.

ALTER TABLE owner.table_name TYPE DEALLOCATE UNUSED is another thing you might want to try. Starting with Oracle 7.3 this command will let you explicitly deallocate unused blocks. Note: Only those empty blocks that are above the high-water mark can be deallocated. You can also use the command on indexes, overflow data-segments and LOB segments. Try using DBMS_SPACE.UNUSED_SPACE procedure to identify a candidate this command would be of benifit.

Good luck.

This discussion is closed to new replies.