chained.sql

rem    Name:   chained.sql
rem
rem    This simple script lists the number of chained rows per table. 
rem    You must have run analyze for your tables with the compute 
rem    option to obtain accurate chaining counts.

column owner format a16
column "Chained Rows" format 99,999
column table_name format a26
column analyzed   format a16

ttitle 'Tables that Are Analyzed'
select owner, table_name,
       decode(nvl(to_char(num_rows), '** Not Analyzed' ),
       '** Not Analyzed', '**> Not Analyzed' , 'OK') "Analyzed"
      , nvl(chain_cnt,0)     "Chained Rows"
  from all_tables
 where owner not in ('SYS', 'SYSTEM')
 order by owner, table_name ;