Overview of Data Blocks


Data Blocks are small units of storage space that in the physical datafile of the Oracle database. Oracle manages these storage units in datafiles. When a query is execute to fetch records from the table, then, it can scan many blocks and records returned by the query can be strored in various data blocks ( Logical storeage structure) that reside in the data file ( physical storage structure).So, when we company these data blocks, we should not mix it with operating system blocks. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The data block size can vary, though the default size are defined in the Oracle initialization parameters init.ora. The parameter that defines the data block size is DB_BLOCK_SIZE and is typically defined as


You can always have multiple block size. The block size is associated with table stapce.  One should also be aware or the parameter DB_CACHE_SIZE while working with multiple block size in Oracle database. You can specify of up to five nonstandard block sizes. Data block size has an impact on the I/O, so while working with the data block size, do consider its impact on I/O, if any performance tuning is being done by a DBA. The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data.

Other Information's related with DATA BLOCK

Block Header contains block information like block address and segment type (data or index).

Table Directory part of data block provides the table information in a block.

Row Directory  address of each rows as row data area in the data block. The space allocated with the row directory is not released if row is deleted. Inspite, this space is re used when new records are inserted int he data block

Row Data This portion of the data block contains table or index data.

Block Overhead Data block header, table directory and row directory are the over heads associated with the blocks. Block overhead can be fixed but total block overhead is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Free Space is allocated for new rows to be inserted or is used for updating existing rows. The null data when updated with ascii value data the free space come in use. When we insert a new rows and for updates to rows that require additional space, This is the PCT_FREE associated with the data block. Free space can also hold transaction entries.
A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space transaction entries in most operating systems require approximately 23 bytes, this is operating system dependent.