Availability and Optimization of Free Space in Oracle

Back to: Oracle DBA Tutorials

Availability and Optimization of Free Space in Oracle

In this article, we will focus on the Availability and Optimization of Free Space in a Data Block in Oracle with Examples. Please read our previous article discussing Segments, Extents, and Data Blocks in Oracle. Two types of statements can create the free space of one or more data blocks.

• DELETE statements
• UPDATE statements that update the existing values to smaller values.

The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

• If the INSERT statement is in the same transaction and subsequent to the statement that frees space, it can use the space made available.
• If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

For example, there is a full block inside the storage. One programmer connected to the database session s1. The programmer performed a delete operation, and then one of the blocks present in the full block was freed up. Then, in the same session, the same programmer performed the insert operation. The inserted data will be stored in the block that was freed up before.

Let us look at another example: Programmer 1 connected to database session s1, and Programmer 2 connected to database session s2. In session s1, the programmer issued a delete statement, and the block was freed up. Then from the second session s2, the programmer performs an insert operation, the inserted data will not occupy the freed block until and unless the programmer from session s1 issues commit statement. If the programmer from s1 doesn’t issue a commit statement, then Oracle uses another free block to store the insert statement data.

In order to store the data in the data block, the data should be a contiguous block. Oracle database automatically and transparently coalesces the free space of a data block only when the following conditions are true:

This is the database block. We have a block header and free space in the white area. Oracle can merge the free space into one unit. But the following conditions need to be true:

• An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece. There are two free spaces, each 1kb. The total size of the data to be inserted is 2kb. Half of the free space is above, and the other half is below. Oracle merges these two free spaces if the data is sufficient in these two blocks.
• The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.

In the next article, I will discuss Row Chaining and Migrating in Oracle. In this article, I explain the Availability and Optimization of Free Space in Oracle with Examples. I hope you enjoy this Availability and Optimization of Free Space in Oracle article.