Jay,length() and lengthb() only work for varchar2 (itherwise you may hit an implicit conversion let's say from date to varchar2, which will result in totally garbage) . be careful in guessing the correct length of any row. I struggled about BUG:4549673 / BUG:7295712 where the distinction between chained and migrated rows is vital. The only clear way I got from Oracle support is a block dump and interpreting this (Note: 122020.1 / Note:102989.1 So after all my research I cannot give you a useful solution. We increased PCTFREE to an insane value of > 90% to make sure a migrated row can never happen. (This was ok in this particular case)
sorry, no good news, just some infos. -- Martin Berger
All I found in the Oracle docs was the suggestion to assume they're migrated and if the fix doesn't work then that means they were really chained ( Note:122020.1).I'm considering using length() on all the columns and adding them together to find any rows that wouldn’t fit in a block but was wondering if there was an easier way. Besides, one of the tables (third party app) has a long raw column so there's no easy way to get the column length there.