A migrated row is a row that was moved to another block due to an update making it too large to fit on its original block with the other rows there.
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:
Location of the row within the block
You can display the address of a row in a table by querying the ROWID pseudo-column.
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:
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).