Re: Please help ---- Oracle free spaces

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 22 Oct 2004 12:13:33 +1000
Message-Id: <41786ccd$0$23895$afc38c87@news.optusnet.com.au>

You have to understand how Oracle allocates space.

Inserts and updates cause tables to acquire new extents. Deletes never cause a table to relinquish extents it has already acquired.

Hence, if I insert a million rows into table A, and it acquires 16 1MB extents, the table A is 16MB big.

If I delete 1 million rows from table A, table A is still 16MB big.

If the tablespace in which this table (and it alone) resides is, say 64MB in size, then after the insert DBA_FREE_SPACE would be reporting 48MB of free space. After the delete, it would also be reporting 48MB of free space.

The only things that cause tables to relinquish their extents is (a) to drop them entirely (b) to truncate them (when all bar MINEXTENTS are relinquished) or (c) manually sit there typing 'alter table X deallocate unused' commands.

Have you read the Oracle concepts manual? (http://tahiti.oracle.com if not).

Regards
HJR Received on Thu Oct 21 2004 - 21:13:33 CDT