Re: Recovering space after delete

The space in a table corresponding to rows that have been deleted is not directly recoverable. i.e. You cannot release this space to other objects in that tablespace and therefore the free_space in that tablespace will not increase as a result.. New rows inserted into that same table can re-use that space however.
The "direct" option of SQL-Loader, however, only uses space above the "high-water mark" and will therefore not insert rows into those deleted spaces.
Rows deleted at the "end" of the table may be released by using an ORACLE 7.3 command, "alter table $table deallocate unused extents"

When rows are deleted, the index segments also end up with deleted rows that do not get used under any circumstances. However, indexes can be easily rebuilt by a new ORACLE 7.3 command, "alter index $index rebuild". It would be advised to include all the storage parameters that were part of the index definition as leaving these out will result in that tablespace storage defaults not the old index storage parameters. This feature is sometimes difficult to use because it uses the existing index while it re-creates a new index and therefore needs 2 X the space until index rebuilding is complere. Received on Mon Sep 08 1997 - 00:00:00 CDT