DB_BLOCK_SIZE - Choose the Right Oracle Block Size

DB_BLOCK_SIZE

Overview

DB_BLOCK_SIZE specifies the size, in bytes, of the standard Oracle database block — the fundamental unit of I/O between the database and storage. Every read and write to data files is performed in multiples of this block size. The value is set permanently when the database is created with CREATE DATABASE and cannot be changed afterward without recreating the database from scratch.

Choosing the right block size requires understanding your workload before the database is built. An undersized block increases I/O frequency for large sequential scans. An oversized block wastes buffer cache memory when rows are small and access is highly random. For most OLTP workloads, 8 KB (the default) is the correct choice; larger blocks benefit data warehouses and environments that store large LOBs.

Parameter Type: Static (set at CREATE DATABASE; read-only thereafter) Default Value: 8192 bytes (8 KB) on all supported platforms Valid Range: 2048, 4096, 8192, 16384, 32768 (platform-dependent; not all values are valid on all OS/storage combinations) Available Since: Oracle 7 (parameter name formalised in Oracle 8i) Modifiable: No — the value is fixed at database creation time and cannot be altered PDB Modifiable: No — PDBs inherit the CDB block size

---

Configuration

Viewing Current Value

-- Current block size (always matches what was set at CREATE DATABASE)
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'db_block_size';

-- Confirm from database properties (cross-check)
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE'
UNION ALL
SELECT 'DB_BLOCK_SIZE', TO_CHAR(block_size)
FROM   (SELECT block_size FROM dba_tablespaces WHERE rownum = 1);

-- Block size for each tablespace (tablespaces can use non-standard block sizes)
SELECT tablespace_name,
       block_size,
       contents,
       status
FROM   dba_tablespaces
ORDER BY block_size, tablespace_name;

-- Derived statistics that depend on block size
SELECT block_size,
       ROUND(block_size / 1024, 0)     AS block_size_kb,
       8192 / block_size               AS blocks_per_8kb_io
FROM  (SELECT TO_NUMBER(value) AS block_size
         FROM v$parameter
        WHERE name = 'db_block_size');

This Parameter Cannot Be Changed

Unlike nearly all other Oracle initialization parameters, DB_BLOCK_SIZE is not settable via ALTER SYSTEM or ALTER SESSION. Attempting to do so raises an error:

-- This will FAIL — shown here so you recognise the error
ALTER SYSTEM SET db_block_size = 16384;
-- ORA-02095: specified initialization parameter cannot be modified

-- To confirm it is listed as non-modifiable
SELECT name, issys_modifiable, isinstance_modifiable
FROM   v$parameter
WHERE  name = 'db_block_size';
-- issys_modifiable = FALSE, isinstance_modifiable = FALSE

The only supported path to a different block size is to:

1. Export data using Data Pump (expdp)
2. Create a new database with the desired block size
3. Import data with Data Pump (impdp)

Non-Standard Block Sizes for Individual Tablespaces

Oracle supports tablespaces with block sizes different from DB_BLOCK_SIZE (2 KB, 4 KB, 16 KB, or 32 KB). This requires a corresponding DB_nK_CACHE_SIZE to be configured in the SGA.

-- Add a 16 KB buffer cache to support 16 KB tablespaces in an 8 KB database
ALTER SYSTEM SET db_16k_cache_size = 256M SCOPE = BOTH;

-- Create a tablespace with a 16 KB block size
CREATE TABLESPACE lob_data_16k
  DATAFILE '/u01/oradata/PROD/lob_data_16k01.dbf'
  SIZE 10G
  BLOCKSIZE 16384;

-- Verify
SELECT tablespace_name, block_size FROM dba_tablespaces
WHERE  tablespace_name = 'LOB_DATA_16K';

---

Tuning Guidance

Choosing the Right Block Size at Database Creation

This decision must be made before running CREATE DATABASE. It cannot be revisited without rebuilding the database.

8 KB (8192 bytes) — Default and most common

Best choice for:

• OLTP workloads (random row-level reads and writes)
• Mixed workloads with no dominant LOB or sequential scan pattern
• Databases where row size is small (< 2 KB)
• Environments where a poor choice will be safe rather than optimal

Characteristics:

• Minimal wasted space in the buffer cache for single-row access
• Good balance between I/O granularity and random-access efficiency
• Matches the default OS page size on most platforms

16 KB (16384 bytes)

Best choice for:

• Data Warehouse / Decision Support (DSS) databases with large full-table scans
• Databases with significant LOB (BLOB, CLOB) storage where LOB segments benefit from larger chunks
• Environments where the average I/O reads multiple rows per block

Characteristics:

• Each I/O brings twice as many bytes as 8 KB blocks — beneficial for sequential scans
• Buffer cache holds fewer blocks for the same memory allocation — may hurt random-access workloads
• Row chaining occurs sooner for very wide rows if rows exceed 16 KB − overhead

32 KB (32768 bytes)

Best choice for:

• Pure data warehouse environments with very wide rows or large LOB columns
• Specific applications documented by the vendor as requiring 32 KB blocks

Characteristics:

• Maximum block size; substantial risk of wasted buffer cache if access is random
• Not supported on all platforms; verify before use
• Row chaining risk for rows wider than approximately 30 KB

2 KB and 4 KB — Avoid for new databases

These were relevant for older storage systems. Modern storage and OS always transfer at least 4 KB per I/O; using a 2 KB or 4 KB Oracle block multiplies I/O overhead. Use 8 KB or larger.

Impact on Row Chaining

Row chaining occurs when a row is too wide to fit in a single block. It degrades performance because Oracle must follow a chain pointer to retrieve the rest of the row.

-- Check for chained or migrated rows
SELECT owner,
       table_name,
       num_rows,
       chain_cnt,
       ROUND(chain_cnt / NULLIF(num_rows, 0) * 100, 2) AS chain_pct,
       avg_row_len,
       (SELECT TO_NUMBER(value)
          FROM v$parameter
         WHERE name = 'db_block_size')                  AS block_size
FROM   dba_tables
WHERE  chain_cnt > 0
  AND  owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY chain_cnt DESC
FETCH FIRST 20 ROWS ONLY;

-- Segment-level block usage efficiency
SELECT segment_name,
       segment_type,
       blocks,
       extents,
       bytes / blocks                                   AS bytes_per_block,
       (SELECT TO_NUMBER(value) FROM v$parameter
         WHERE name = 'db_block_size')                  AS db_block_size
FROM   dba_segments
WHERE  owner = 'YOUR_SCHEMA'
  AND  segment_type = 'TABLE'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

Buffer Cache Efficiency

Larger blocks mean fewer blocks fit in the buffer cache for the same memory footprint when access is random. Check the buffer cache hit ratio:

-- Buffer cache hit ratio (should be > 95% for OLTP)
SELECT name,
       physical_reads,
       db_block_gets,
       consistent_gets,
       ROUND(
         (1 - physical_reads / NULLIF(db_block_gets + consistent_gets, 0)) * 100,
         2
       ) AS hit_ratio_pct
FROM   v$buffer_pool_statistics;

-- Block size related wait events
SELECT event,
       total_waits,
       time_waited,
       average_wait
FROM   v$system_event
WHERE  event IN ('db file sequential read', 'db file scattered read',
                 'db file parallel read')
ORDER BY time_waited DESC;

---

Common Issues

Issue 1: Application performance unexpectedly poor after database migration

Symptom: After migrating from a database with one block size to another (e.g., 4 KB to 8 KB), query performance degrades or the buffer cache is less efficient.

Cause: If the original database used a non-standard block size that matched the application’s I/O pattern, moving to a different block size changes the number of rows per block and therefore the selectivity of block-level locks and cache usage.

Resolution: There is no runtime fix — the block size is fixed. Profile the new database with AWR or ADDM to find specific hot objects and apply compensating optimisations (partitioning, result caching, index changes). For future migrations, evaluate block size requirements during the planning phase.

Issue 2: ORA-01578 — Data block corruption detected

Symptom: ORA-01578: ORACLE data block corrupted (file # N, block # M) during a query or DML operation.

Cause: Block-level corruption can occur due to storage media failures, I/O subsystem bugs, or incomplete writes. The block size itself is not a cause of corruption, but it determines the granularity of corruption detection and repair.

Resolution: Use RMAN block media recovery to repair individual corrupt blocks without restoring the entire datafile:

-- Identify corrupted blocks
SELECT file#, block#, blocks, corruption_change#, corruption_type
FROM   v$database_block_corruption;

-- From RMAN:
-- RMAN> RECOVER CORRUPTION LIST;
-- OR repair a specific block:
-- RMAN> RECOVER DATAFILE 5 BLOCK 1234;

Issue 3: Cannot create tablespace with desired block size — ORA-29339

Symptom: ORA-29339: tablespace block size N does not match configured block sizes when trying to create a non-standard block size tablespace.

Cause: The corresponding DB_nK_CACHE_SIZE parameter has not been set.

Resolution:

-- For a 16 KB tablespace, configure the 16 KB buffer pool first
ALTER SYSTEM SET db_16k_cache_size = 128M SCOPE = BOTH;
-- Then retry the CREATE TABLESPACE statement

---

Related Parameters

• DB_CACHE_SIZE — Size of the default buffer pool; holds blocks of size DB_BLOCK_SIZE.
• DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32K_CACHE_SIZE — Additional buffer pools required to support non-standard block size tablespaces.
• DB_FILE_MULTIBLOCK_READ_COUNT — Number of consecutive blocks read in a single I/O for full-table scans; interacts with block size to determine effective I/O size.

---

Related Errors

• ORA-01578 — Data block corrupted; RMAN block media recovery required.
• ORA-02095 — Specified initialization parameter cannot be modified; raised if you attempt ALTER SYSTEM SET db_block_size.
• ORA-29339 — Non-standard block size tablespace creation fails because the matching buffer pool is not configured.

---

Version Notes

Version	Notes
Oracle 8i	DB_BLOCK_SIZE parameter name formalised; non-standard block size tablespaces introduced requiring DB_nK_CACHE_SIZE.
Oracle 9i	Transport of tablespaces with different block sizes between databases enabled (Transportable Tablespaces with different block sizes).
Oracle 10g	No changes; 8 KB remains the default and recommended size for most workloads.
Oracle 12c	In a CDB, the CDB block size is inherited by all PDBs; individual PDBs cannot have a different DB_BLOCK_SIZE from their CDB.
Oracle 19c+	Applies to Autonomous Database: Autonomous Transaction Processing uses 8 KB; Autonomous Data Warehouse uses 8 KB with 32 KB LOB segments.