RelationalDBDesign RelationalDBDesign 

Data Blocks  «Prev  Next»

Oracle Data Block Fundamentals

The following gives insight into Data block fundamentals.
  1. Freelist: Used to manage free data blocks
  2. High water mark: Used to track the last data block with row data
  3. PCTFREE: Determines when to perform a freelist un-link
  4. PCTUSED: Determines when to perform a freelist re-link
  5. Extents table: Used to manage new object extents

Row Storage in Data Blocks and Segments: A Primer

The database stores rows in data blocks. In tables, the database can write a row anywhere in the bottom part of the block. Oracle Database uses the block overhead, which contains the 1) row directory and 2) table directory, to manage the block itself. An extent is made up of logically contiguous data blocks.
The blocks may not be physically contiguous on disk. A segment is a set of extents that contains all the data for a logical storage structure within a tablespace. For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table. By default, the database uses (ASSM) automatic segment space management for permanent, locally managed tablespaces. When a session first inserts data into a table, the database formats a bitmap block. The bitmap tracks the blocks in the segment. The database uses the bitmap to find free blocks and then formats each block before writing to it. ASSM spread out inserts among blocks to avoid concurrency issues.
Logical and Physical Storage

The (HWM) high water mark is the point in a segment beyond which data blocks are unformatted and have never been used. Below the HWM, a block may be formatted and written to, formatted and empty, or unformatted. The low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data. During a full table scan, the database reads all blocks up to the low HWM, which are known to be formatted, and then reads the segment bitmap to determine which blocks between the HWM and low HWM are formatted and safe to read. The database knows not to read past the HWM because these blocks are unformatted.

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 and 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 initialization parameter DB_BLOCK_SIZE. In addition, you can specify of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.