From:Steve Adams
Date:27-Jul-2001 15:29
Subject:   Row migration

I'll force a row to migrate to demonstrate what happens. Here's what my row looks like in a block dump beforehand ...

        tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
        col  0: [ 2]  c1 02
        col  1: [ 1]  20
The total length of the row is 8 bytes - 3 for the row header and 5 for the data. The row header contains a flag byte, a lock byte and a column count. The flag byte shows that this row piece is the head row piece (H), the first row piece (F) and the last row piece (L) of its row - that is, it is the whole row. The column count is 2, meaning that there is column data for two columns. Here's what it looks like after migration ...
        tl: 9 fb: --H----- lb: 0x0 cc: 0
        nrid:  0x00c05858.1
The total length of the row is now 9 bytes - 3 for the row header and 6 for the rowid of the next row piece in the row. The flag byte shows that this is just the head row piece and the column count is 0, meaning that there is no column data here. Note that Oracle is able to use the 6-byte restricted rowid format here - 4 bytes for a tablespace relative data block address and two bytes for a row number - because rows can only migrate within the tablespace that contains their table. If we follow that rowid, we see that the next row piece looks like this ...
        tl: 115 fb: ----FL-- lb: 0x0 cc: 2
        hrid: 0x00c05857.0
        col  0: [ 2]  c1 02
        col  1: [100]
         20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
         ...
The flag byte shows that this row piece is the first and last of its row, but not the header. You'll also see that the rowid of the head row piece is stored here, making this row piece 6 bytes longer than it might otherwise be.

When the row is migrated again, we do not get a chain of leading zero-column row pieces as you might imagine. Instead, Oracle changes the next rowid pointer in the head row piece to point to the new location of the row body as follows ...

        tl: 9 fb: --H----- lb: 0x0 cc: 0
        nrid:  0x00c05859.0
The new row piece in that location for the row body looks much the same, except that it's bigger and I've not yet committed the change, so the lock byte is still set.
        tl: 215 fb: ----FL-- lb: 0x1 cc: 2
        hrid: 0x00c05857.0
        col  0: [ 2]  c1 02
        col  1: [200]
         20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
         ...
And a 2-byte stub is left in place of the old row piece for the row body as follows ...
        tl: 2 fb: ---DFL-- lb: 0x1
The D flag shows that this row piece has been deleted.

So even if a row migrates many times, it will always consist of just its head row piece and a single body row piece. This remains true as long as the body row piece can fit in an empty (newly formatted) block.

Is there any upper limit to the number of times a given row can be migrated into new blocks? What happens to the "forwarding address" chain for this row? Does it just keep getting longer and longer?