SEO

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.

<agarwalp@eeism.com> wrote in message
news:1117625652.469632.285630@g49g2000cwa.googlegr oups.com...
> 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.
>

It may be used the next time you perform an insert, depending on the state
of the free block list. Notice the word may.

agarwalp@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

Agarw, I think Tim hit upon the key point for when space can be reused
within a block and by which sessions. It depends on when the session
performing DML commits or rollsback. In the future if you post a
question about something in the documentation you should probably
identify the chapter and section topics if you do not post the actual
text so that board readers can better frame their responses.

HTH -- Mark D Powell --