I have a very large table, but it only has a few rows left in it. How can I reclaim the space ?

The Oracle (tm) Users' Co-Operative FAQ

I have a very large table, but it only has a few rows left in it. How can I reclaim the space ?

The answer to "Can I reclaim the space?" is always a resounding "YES". However a more appropriate question may be "Can I reclaim the space cheaply" to which the answer (as it always tend to be) is "maybe..."

Since Oracle 7.3, the "ALTER TABLE" command has included the option to release unused space from a table. The command is:

alter table MY_TABLE deallocate unused keep xx

(where xx is the amount of table free space you wish to retain)

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.

Of course, if you're not so lucky, and the rows are dispersed widely across the table blocks, then they will need to be relocated to the "front" of the table to reclaim the space, and "relocated" means work. The easiest way to do this is to be on 8.1+, where you can issue

alter table MY_TABLE move

or even better, if you're on 9.0+

alter table MY_TABLE move online

Unfortunately, if you're on a release prior to 8.1, you're up for the standard unload/reload practices, namely export/import or create-table-as-select et al.

Further reading: (all via technet) "ALTER TABLE MOVE" command and "Export/Import" utilities