Re: Free space management in Blocks

agarwalp_at_eeism.com wrote:
> I read in the Oracle 9i Concepts document, that a free space generated
> by deleting or updating of data can be used only by an insert statement
> in the same transaction as of delete/update or any insert statement
> immediately after the DML statement. So does that mean if there is no
> immediate insert statement that free space will never be used ? Please
> clarify my doubt.

No, it doesn't have to be *immediately* after the DELETE or UPDATE.

What happens is that when a DELETE or UPDATE frees up space in a data block, that space is only available to the transaction which freed it UNTIL the transaction commits. After the transaction commits, if the data block has dropped below the PCTUSED parameter then it is placed on to the segment's freelist and is available for INSERTs.

Now, if an INSERT statement finds enough free space within the block to accommodate a row (whether that space is contiguous or not) it will use it. This does not have to be *immediately* after the transaction that caused the space to become available but at any later stage.

Note that Oracle will coalesce (i.e. make contiguous) the free space in the block if it is necessary to do so to accommodate the row.

Note also that the above discussion relates to "conventional" space management. If automatic segment space management (ASSM) is specified then Oracle manages space within blocks via a bitmap mechanism rather than freelist structures.

Hope that helps.

Regards,

Tim Kearsley
HBS Milton Keynes Received on Wed Jun 01 2005 - 09:29:54 CDT