Data Blocks  «Prev

Oracle Migrating and Chaining

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).

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: 
ROWID EMP_ID
------------------ ----------
AAAEtQAAEAAAACDAAA 100
AAAEtQAAEAAAACDAAB 101
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: 
select emp_id
,dbms_rowid.rowid_to_absolute_fno(rowid,schema_name=>
'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: 
EMP_ID FILE_NUM BLOCK_NUM ROW_NUM
---------- ---------- ---------- ----------
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).
Advanced Oracle SQL Tuning