Data Blocks  «Prev

Migrating and Chaining in Oracle

A migrated row is a row that was moved to another block due to an update which made the row too large to fit on its original block where the other rows already existed. We are unable to "move" the row since we have lots of indexes pointing to the original block.
We therefore leave behind a forwarding address on the original block and migrate the updated row to a new block.
When you access that row, we discover it is not really there.
The row has migrated and we read the other block to get it.
A chained row is a row that is too large to fit on a single block. If you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces. Any table with a long raw will have chained rows.
Any table whose rowsize exceeds the blocksize will have chained rows. Any table with more then 255 columns will have chained rows (we break really wide tables up).

If PCTFREE is set low enough so that the rows cannot be stored on the same block (A), but on another block (B).

Oracle moves the entire row to the new block (B), and the rows are said to be migrated.

If PCTFREE is set too low, so that the row is too large to fit into any available block caption

Oracle splits the row into multiple pieces and stores each in a separate block, and the rows are said to be chained.

How do you know if the migrated or chained rows have been fixed?
Repeat the process of ANALYZE TABLE ...
LIST CHAINED ROWS. If new rows are created in the CHAINED_ROWS table then this means you most likely have chained rows (and not migrated rows) and these can only be resolved by moving the table and adjusting PCTFREE to a lower value or moving the table to a tablespace that has a larger block size .

Every row in every table has a physical address. The address of a row is determined from a combination of the following:
  1. Datafile number
  2. Block number
  3. Location of the row within the block
  4. Object number

You can display the address of a row in a table by querying the ROWID pseudo-column.
For example:
SQL> select rowid, emp_id from emp;

Here is some sample output:
------------------ ----------
The ROWID pseudo-column value is not physically stored in the database. Oracle calculates its value when you query it. The ROWID contents are displayed as base-64 values that can contain the characters
A-Z, a-z, 0-9, +, and /.

You can translate the ROWID value into meaningful information via the DBMS_ROWID package. For example, to display the file number, block number, and row number in which a row is stored, issue this statement:
Advanced Oracle SQL Tuning
select emp_id
'MV_MAINT',object_name=>'EMP') file_num
,dbms_rowid.rowid_block_number(rowid) block_num
,dbms_rowid.rowid_row_number(rowid) row_num
from emp;

Here is some sample output:
---------- ---------- ---------- ----------
100 4 131 0
101 4 131 1
You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the ROWID uniquely identifies a row. However, it is possible to have rows in different tables that are stored in the same cluster and so contain rows with the same ROWID (like with a clustered table).