Introduction to Data Blocks, Extents, and Segments

Overview of Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter

Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2illustrates the format of a data block.
Figure 2-2 Data Block Format
Description of Figure 2-2 follows

PCTFREE, PCTUSED, and Row Chaining

The PCTFREE Parameter

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:

This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size. Figure 2-3illustrates PCTFREE.
Figure 2-3 PCTFREE
Description of Figure 2-3 follows

The PCTUSED Parameter

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a CREATE TABLE statement:

In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 2-4 illustrates this.
How PCTFREE and PCTUSED Work Together
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 2-5 illustrates the interaction of these two parameters.
Figure 2-5 Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED
Description of Figure 2-5 follows
After you issue a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions
Source: ORACLE Documents


Popular posts from this blog

Oracle APPS Useful Queries

How to Apply Timers In Forms

Unix Shell Script For Oracle Apps