Re: Incorrect Migrated/Chained rows...

From: MAK <maks70_at_comcast.net>
Date: 29 Apr 2004 15:52:23 -0700
Message-ID: <b7178504.0404291452.be1ea4a@posting.google.com>

OK! I learnt quite a few things from that site. Thanks. Finally I managed to get the correct block dump.

From the block dump, it looks like reported rows is infact a migrated row(Without update though....). And the reason its a migrated row because it is the last row in the block. It appears oracle is calculating the total length of row piece ( rather than a full row ) while deciding if row inserted in to same block or different block. Is this a normal behaviour?

Below is the relevant part of the block dump. Here is query I used to identify the block,file & row. As I mentioned in my earlier post TEMP_S_CONTACT_05 has only 1 row repoted as chained/migrated. I took dump of several blocks and this is true for

select dbms_rowid.rowid_block_number(rowid) blkno,
       dbms_rowid.rowid_RELATIVE_FNO(rowid) relfno,
       dbms_rowid.ROWID_ROW_NUMBER(rowid) rownu,
       dbms_rowid.ROWID_TO_RESTRICTED(rowid,0) rid from

mars7.temp_s_contact_05
where rowid in (select head_rowid from mars7.chained_rows where table_name = upper('temp_s_contact_05')) /

     BLKNO RELFNO ROWNU RID

---------- ---------- ---------- ------------------
    629744         68         26 00099BF0.001A.0044

SQL>alter system dump datafile 68 block 629744 ;

As per the above, row 26 in block 629744 is the MIGRATED row.

Here how I interpreted block dump data :

fb: H means header, F means First colum, L means last column. So if you have intact row ( row = 1 row piece ) you'd see flag fb: similar to H-FL. When you have multiple row pieces in a row, you would see fb: flag as H-F for first piece and ---L- in the last row piece.

cc: Column count.

nrid: Next row id in case of multiple pieces/chained/migrated rows.

Since in this case, nos of columns is > 255, for all the rows I see are having two row pieces. 1st piece (255 columns ) with fb: --H-F-- and 2nd piece(43 columns) with fb: ----L-. 2nd piece also have nrid column that shows next rowid ( How do I translate this to restricted or extended rowid? ) .

For all the rows but the last row , first part of nrid value is same e.g.0x11099bf0. That makes me believe that its in the same block and these rows are not reported as chained rows eventhough they have multiple row pieces.
Last row (26) has nrid value 0x11c0133f.0 that appears to be refering to different block. and that's reported as chained row.

tab 0, row 0, @0x1d7c
tl: 476 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.1
tab 0, row 1, @0x1d33
tl: 73 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 2, @0x1b88
tl: 427 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.3
tab 0, row 3, @0x1b46
tl: 66 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 4, @0x198a
tl: 444 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.5
tab 0, row 5, @0x1951
tl: 57 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 6, @0x174e
tl: 515 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.7
tab 0, row 7, @0x16eb
tl: 99 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 8, @0x152d
tl: 446 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.9
tab 0, row 9, @0x14e8
tl: 69 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 10, @0x1324
tl: 452 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.b
tab 0, row 11, @0x12df
tl: 69 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 12, @0x10c9
tl: 534 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.d
tab 0, row 13, @0x107d
tl: 76 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 14, @0xe8a
tl: 499 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.f
tab 0, row 15, @0xe3b
tl: 79 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 16, @0xbfe
tl: 573 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.11
tab 0, row 17, @0xbaa
tl: 84 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 18, @0x9e0
tl: 458 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.13
tab 0, row 19, @0x99a
tl: 70 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 20, @0x760
tl: 570 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.15
tab 0, row 21, @0x70f
tl: 81 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 22, @0x53d
tl: 466 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.17
tab 0, row 23, @0x4f2
tl: 75 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 24, @0x2ff
tl: 499 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11099bf0.19
tab 0, row 25, @0x2b1
tl: 78 fb: -----L-- lb: 0x0 cc: 43
tab 0, row 26, @0x4e
tl: 493 fb: --H-F--- lb: 0x0 cc: 255
nrid: 0x11c0133f.0

Thanks Received on Thu Apr 29 2004 - 17:52:23 CDT