The Oracle (tm) Users' Co-Operative FAQ

I have deleted 6M rows from a table - how can I reclaim the space that is now free ?

Author's name: Jonathan Lewis

Author's Email:

Date written: 24th Jan 2003

Oracle version(s): 7.3 - 9.2

I have deleted 6 million rows from a table, and would like to be able to use the space for other objects. How can I reclaim it from the table and give it back to the database ?

Back to index of questions

The only way to make this space available for other objects to use is to use some sort of rebuild on the table.

The fastest option is probably:

    Alter table XXXX move nologging;

This will recreate the table and repack all the data.  Once you have done this, you should take a backup of at least the tablespace that the table is in as the nologging command means that you could not recover the table from an earlier backup if the system crashed.

To avoid the backup, you can use:

    alter table XXX move;

After you have used the move command (in either form) you will have to rebuild all the indexes on that table.  Again you can choose to do this with or without the nologging command.

    Alter index XXX_idx1 rebuild nologging;
    Alter index XXX_idx1 rebuild;

In the case of indexes, it is less important to take the backup as you don't lose information if the index is accidentally destroyed - you don't need to recover them, you can always rebuild them. Don't forget that you might want to increase your sort_area_size whilst rebuilding the indexes.

You shoudl also remember that if your table rows are subject to significant growth after they have been inserted you will have to work out a more subtle strategy for rebuilding the table. If you do a simple rebuild, then you will either end up wasting a lot of space, or you will find that the table starts to acquire migrated rows as time passes. The details of (and one resolution to) this problem can be found in Practical Oracle 8i.

Further reading: N/A

Back to top

Back to index of questions