Oracle Data Block Row Storage: Mechanism and Verification Steps

English

Introduction (Summary)
In oracle, row storage inside a data block consists of “block header / directory / row data / free space.” When rows are updated or grow in size, row chaining and row migration can occur. This article walks through the mechanism → the shortest verification steps → mitigations, with beginner-friendly, hands-on checks.

Conclusion & Quick Steps (Featured Snippet-Ready)

  1. Understand block layout: header (including ITL) / table & row directory / row data / free space.
  2. Assess current state: check db_block_size via v$parameter; detect chaining/migration using ANALYZE ... LIST CHAINED ROWS.
  3. Identify the cause:
    • A single row cannot fit in one block → row chaining.
    • An update no longer fits in the original block → row migration (a forwarding pointer remains in the original block).
  4. Basic remedies: tune PCTFREE; review row design (split large columns; use LOBs appropriately); reorganize the table if necessary (ALTER TABLE ... MOVE, etc.; beware of ROWID changes).
  5. Ongoing monitoring: periodically use ANALYZE ... LIST CHAINED ROWS (UTLCHAIN.SQL). Note that DBMS_STATS does not populate CHAIN_CNT.

Background and Basics

What is a data block?

The data block is Oracle’s smallest logical storage unit. A single block contains multiple row pieces, and their positions are referenced from the row directory. The internal ROWID structure (datafile number / block number / row number) maps to this layout.

Internal layout of a block (text diagram)

＋－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＋
｜Block Header (common/variable header, ITL and control info)                 ｜
＋－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＋
｜Table Directory         ｜Row Directory (array of row pointers)            ｜
＋－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＋
｜                           Row Data Area                                   ｜
｜   [row piece/row header] [column data] ...                                ｜
＋－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＋
｜                           Free Space (PCTFREE)                            ｜
＋－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＋
  • ITL (Interested Transaction List): slots used to coordinate concurrent transactions within the same block.
  • Row header and NULLs: a trailing run of NULLs is not stored; a NULL in the middle of variable-length columns typically takes 1 byte to indicate length 0.

What are “Row Chaining” and “Row Migration”?

  • Row Chaining: a single row cannot fit in one block, so its pieces span multiple blocks. Typical when you have several large variable-length columns (e.g., multiple VARCHAR2(4000)) or extended strings.
  • Row Migration: when an update increases the row size and it no longer fits in the original block, the entire row moves to another block. A forwarding address remains at the original location, and the ROWID generally stays the same (exceptions exist, e.g., after MOVE).

Tip: LOB columns usually store a “locator” inline and the LOB body in a separate segment (SecureFiles by default). So “big LOB = always chained” is not necessarily true.

Steps & Implementation (works on a real system, including verification)

Assumptions (19c non-CDB / Oracle Linux / default block size):

  • You can use SQL*Plus or SQLcl as SYS.
  • The examples use the generic USERS tablespace (adjust if needed).

0) Pre-check: confirm the block size (read-only)

Goal: confirm your environment’s db_block_size to gauge the behavior in later steps.

-- Block size (bytes)
SELECT value AS db_block_size_bytes
FROM   v$parameter
WHERE  name = 'db_block_size';

Explanation: The default block size (e.g., 8192 = 8KB) is chosen at database creation time. It indicates whether a single row could fit into a block. This is a read-only and safe check.

1) Create a test user (grant privileges and quota)

Goal: isolate the work and make it easier to troubleshoot privilege/quota issues. This is a change (user creation), but you can drop it later.

-- Run as SYS (change the password per your policy)
CREATE USER blkdemo IDENTIFIED BY Demo#1234
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO blkdemo;
ALTER USER blkdemo QUOTA UNLIMITED ON users;

Explanation: Prepare a dedicated schema with CREATE TABLE and a quota on USERS. This step does not affect other objects.

2) Reproduce row chaining: create a table and insert data

Goal: intentionally create rows that do not fit in a single block (to induce row chaining).

-- Run as BLKDEMO (use this schema hereafter)
CREATE TABLE t_chain_demo (
  id NUMBER PRIMARY KEY,
  c1 VARCHAR2(4000),
  c2 VARCHAR2(4000)
) PCTFREE 10; -- default setting

INSERT INTO t_chain_demo
SELECT LEVEL,
       RPAD('X',4000,'X'),
       RPAD('Y',4000,'Y')
FROM dual CONNECT BY LEVEL <= 10;

COMMIT;

Explanation: Two VARCHAR2(4000) columns filled to capacity make each row too large for an 8KB block. RPAD generates fixed-length strings. PCTFREE 10 leaves 10% of free space for future updates (default here).

3) Reproduce row migration: create a table and update a row

Goal: increase the row size through an update so it no longer fits in the original block (to observe row migration).

CREATE TABLE t_mig_demo (
  id  NUMBER PRIMARY KEY,
  pad VARCHAR2(1000)
) PCTFREE 0; -- minimize free space to induce migration

INSERT INTO t_mig_demo
SELECT LEVEL, RPAD('A',10,'A')
FROM dual CONNECT BY LEVEL <= 1000;

COMMIT;

-- Enlarge only one row (likely to trigger migration)
UPDATE t_mig_demo
   SET pad = RPAD('B',1000,'B')
 WHERE id = 1;

COMMIT;

Explanation: With PCTFREE 0, inserts leave almost no free space in the block; later, expanding pad from 10 to 1000 makes it unlikely to still fit in the original block, which induces migration.

4) Create the output table (CHAINED_ROWS)

Goal: create the CHAINED_ROWS table (once) as the destination for ANALYZE ... LIST CHAINED ROWS. The analysis itself is read-only for target tables.

-- Run once as SYS: creates CHAINED_ROWS via UTLCHAIN.SQL
-- Path depends on ORACLE_HOME
@?/rdbms/admin/utlchain.sql

Explanation: This script creates CHAINED_ROWS (owned by the executing user) and a synonym. If it already exists, the script skips creation.

5) Detect chaining/migration (ANALYZE) and review the results

Goal: extract rows with chaining/migration into CHAINED_ROWS and review counts and sample ROWIDs.

-- Run as BLKDEMO: writes results to CHAINED_ROWS (does not modify target tables)
ANALYZE TABLE blkdemo.t_chain_demo LIST CHAINED ROWS;
ANALYZE TABLE blkdemo.t_mig_demo   LIST CHAINED ROWS;

Explanation: LIST CHAINED ROWS detects both chained and migrated rows. It does not modify the target tables (safe, read-only extraction).

-- Count by table
SELECT owner_name,
       table_name,
       COUNT(*) AS rows_flagged
FROM   chained_rows
WHERE  owner_name = 'BLKDEMO'
GROUP  BY owner_name, table_name
ORDER  BY table_name;

Explanation: Count flagged rows per table. Typically, T_CHAIN_DEMO has multiple hits, while T_MIG_DEMO shows around one.

Command execution example

SQL> SELECT owner_name,
  2         table_name,
  3         COUNT(*) AS rows_flagged
  4  FROM   chained_rows
  5  WHERE  owner_name = 'BLKDEMO'
  6  GROUP  BY owner_name, table_name
  7  ORDER  BY table_name;

OWNER_N TABLE_NAME           ROWS_FLAGGED
------- -------------------- ------------
BLKDEMO T_CHAIN_DEMO                   10
BLKDEMO T_MIG_DEMO                      1
-- Sample HEAD_ROWID (first 5 rows)
SELECT table_name, head_rowid
FROM   chained_rows
WHERE  owner_name = 'BLKDEMO'
FETCH FIRST 5 ROWS ONLY;

Explanation: HEAD_ROWID is the ROWID of the first row piece (the “head”), i.e., the original block. For migration, a forwarding pointer remains at the original location, and this is what gets recorded.

Command execution example

SQL> SELECT table_name, head_rowid
  2  FROM   chained_rows
  3  WHERE  owner_name = 'BLKDEMO'
  4  FETCH FIRST 5 ROWS ONLY;

TABLE_NAME           HEAD_ROWID
-------------------- ------------------
T_CHAIN_DEMO         AAAR4BAAHAAAAFbAAA
T_CHAIN_DEMO         AAAR4BAAHAAAAFdAAA
T_CHAIN_DEMO         AAAR4BAAHAAAAFoAAA
T_CHAIN_DEMO         AAAR4BAAHAAAAFpAAA
T_CHAIN_DEMO         AAAR4BAAHAAAAFsAAA
-- Decompose ROWID (file/block/row)
SELECT table_name,
       head_rowid,
       DBMS_ROWID.ROWID_RELATIVE_FNO(head_rowid) AS file_no,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(head_rowid) AS block_no,
       DBMS_ROWID.ROWID_ROW_NUMBER(head_rowid)    AS row_no
FROM   chained_rows
WHERE  owner_name = 'BLKDEMO'
  AND  ROWNUM <= 5;

Explanation: Using DBMS_ROWID, break ROWIDs into file / block / row number for physical placement insights.

Command execution example

SQL> SELECT table_name,
  2         head_rowid,
  3         DBMS_ROWID.ROWID_RELATIVE_FNO(head_rowid) AS file_no,
  4         DBMS_ROWID.ROWID_BLOCK_NUMBER(head_rowid) AS block_no,
  5         DBMS_ROWID.ROWID_ROW_NUMBER(head_rowid)    AS row_no
  6  FROM   chained_rows
  7  WHERE  owner_name = 'BLKDEMO'
  8    AND  ROWNUM <= 5;

TABLE_NAME           HEAD_ROWID            FILE_NO   BLOCK_NO     ROW_NO
-------------------- ------------------ ---------- ---------- ----------
T_CHAIN_DEMO         AAAR4BAAHAAAAFbAAA          7        347          0
T_CHAIN_DEMO         AAAR4BAAHAAAAFdAAA          7        349          0
T_CHAIN_DEMO         AAAR4BAAHAAAAFoAAA          7        360          0
T_CHAIN_DEMO         AAAR4BAAHAAAAFpAAA          7        361          0
T_CHAIN_DEMO         AAAR4BAAHAAAAFsAAA          7        364          0
-- (Reference) Cross-check affected rows with table data
SELECT 'CHAIN' AS cause, t.id, t.c1, t.c2
FROM   blkdemo.t_chain_demo t
WHERE  t.ROWID IN (
  SELECT head_rowid
  FROM   chained_rows
  WHERE  owner_name = 'BLKDEMO'
  AND    table_name = 'T_CHAIN_DEMO'
)
UNION ALL
SELECT 'MIG', m.id, m.pad, CAST(NULL AS VARCHAR2(1))
FROM   blkdemo.t_mig_demo m
WHERE  m.ROWID IN (
  SELECT head_rowid
  FROM   chained_rows
  WHERE  owner_name = 'BLKDEMO'
  AND    table_name = 'T_MIG_DEMO'
);

Explanation: Join CHAINED_ROWS.HEAD_ROWID to the table ROWID to identify which rows are affected. For migration, the original ROWID is returned because the forwarding pointer remains at the original block.

Note: You can differentiate chaining vs. migration definitively via block dumps, but operationally it’s enough to infer from table design and update patterns, then apply remedies (PCTFREE/column design/reorg).

Sample Results (How to Interpret)

  • v$parameter: db_block_size_bytes = 8192 (example)
  • CHAINED_ROWS: multiple records for T_CHAIN_DEMO, ~1 record for T_MIG_DEMO.

Operational reading:

  • Chaining always implies multiple block accesses, which increases I/O for both full scans and random access.
  • Migration implies a two-step access (original block → destination). Even indexed lookups can involve chasing the forwarding pointer; the CBO may not always anticipate this cost.

Performance Tuning Tips (Key Points)

  1. Tune PCTFREE: for tables whose rows frequently grow, reserve more free space to reduce migration.
  2. Review row design: split very large variable-length columns; normalize if appropriate.
  3. LOB optimization: use SecureFiles (default) and understand inline vs. out-of-line storage.
  4. Plan reorgs carefully: ALTER TABLE ... MOVE [UPDATE INDEXES] can change ROWIDs (with exceptions like some IOT cases). Beware if applications rely on ROWID.

Troubleshooting (Common Cases)

Symptom / ErrorLikely CauseChecks / Resolution
ORA-01495 when querying CHAINED_ROWSCHAINED_ROWS not created / privilegesRun @?/rdbms/admin/utlchain.sql as SYS to create the table and synonyms.
ORA-01496Mismatch in CHAINED_ROWS definitionRecreate with the version-appropriate UTLCHAIN.SQL.
Too many chained/migrated rowsRow design / update patterns / suboptimal PCTFREEUse ANALYZE ... LIST CHAINED ROWS to identify affected rows → review design, PCTFREE, and LOB strategy.
Performance drop after reorgIndex/statistics inconsistencyUse ALTER TABLE ... MOVE UPDATE INDEXES and refresh statistics.

Operations, Monitoring, and Security Notes

  • Do not use ROWID as a long-term key: MOVE / SHRINK / physical relocations can change ROWIDs. Use logical business keys instead.
  • Divide and conquer: for hot tables with high rates of chaining/migration, correlate with DDL logs and change history to pinpoint causes (data modeling vs. app updates).
  • Prefer read-only verification first: ANALYZE ... LIST CHAINED ROWS lets you assess trends without modifying target tables.

FAQ

Q1. Will changing the block size from 8KB to 16KB later reduce chaining?
A. The database’s default block size cannot be changed later. You’d need a new DB design or advanced setups like non-default block-size tablespaces with dedicated buffers. Start with row design and PCTFREE.

Q2. Are LOBs the main cause of chaining?
A. Often the LOB body is out-of-line (separate segment) and only a locator is stored in the row. Chaining is more commonly caused by combinations of large variable-length columns.

Q3. Does DBA_TABLES.CHAIN_CNT get populated by DBMS_STATS?
A. Generally no. Use ANALYZE ... LIST CHAINED ROWS or ANALYZE ... COMPUTE STATISTICS to populate/inspect it.

Q4. Does a row’s ROWID always stay the same with migration?
A. Usually yes for updates that cause migration, but ROWIDs do change with MOVE/SHRINK or partition moves (when ROW MOVEMENT is enabled).

Summary (Key Takeaways)

  • A data block has header / directory / data / free space; ITL and row directory are critical.
  • Chaining = row cannot fit one block; Migration = updated row no longer fits the original block.
  • Use ANALYZE ... LIST CHAINED ROWS for detection; DBMS_STATS does not populate CHAIN_CNT.
  • The first remedies are PCTFREE and row design; understand LOB locators vs. out-of-line storage.
  • Reorgs can change ROWIDs; plan along with indexes and stats.

This article targets Oracle Database 19c (other versions may differ in screens and defaults).


[reference]
Oracle Database Database Performance Tuning Guide, 19c

A Complete Guide to Creating Tables in Oracle Database [CREATE TABLE Explained]
Creating tables is one of the most fundamental skills when working with Oracle Database.This article provides a thorough...
oracle-master00000.com
2025.07.30

コメント

Copied title and URL