How you can identify the Chained and Migrated rows

How you can identify the Chained and Migrated rows ?
+background

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

1-ANALYZE

You can use the ANALYZE command to list the Chained/Migrated rows in a table, but you should consider the followings:

A-For conventional Non index_organized tables you can create chained-rows table using the following script locally or in another schema:

   create table CHAINED_ROWS (
   owner_name         varchar2(30),
   table_name         varchar2(30),
   cluster_name       varchar2(30),
   partition_name     varchar2(30),
   subpartition_name  varchar2(30),
   head_rowid         rowid,
   analyze_timestamp  date );

Note : If you created the chained-rows table in another schema then in the analyze command prefix the name by the schema name.

analyze table  list chained rows into  ;

B-For index_organized tables you have to create separate chained-rows table for each table to be analyzed as follows:

exec DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('',
'',
'');
analyze table list chained rows into ;

select * from ;

SQL> select count(*) from chained_rows where table_name like '<>';


2-V$SYSSTAT

You can also detect migrated and chained rows by checking the 'table fetch continued row' statistic in
the v$sysstat view.

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

3-USER_TABLES

The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt,
       round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM user_tables
 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';