# 
# Program:   chain.sql   (Version 6 Oracle)
# 
# Author:    Mark Gurry
# 
# Purpose:   This script is used to detect chaining within an Oracle
# 	   table. To run the script
# 
# 	   sqlplus un/pw @chain
# 
#            The script will then prompt you for a Table Name.
# 
# 	   A Temporary Dynamic SQL Script ch.sql is created
#            for the selectd table. This script will then list
#            all rows that are chaining. If you want to find 
#            which actual rows are chained, you can list them with
# 
#            select * from table where substr(rowid,1,8) = '$$$$$$$$'
#            n.b.  $$$$$$$$ is the rowid shown for the chained block.
# 
#
set heading off
set verify off
set feedback off
set echo off
set embedded on
set pagesize 9999
break on blk;
set recsep off
set linesize 150;
column blk format a60;
column rest format a80 ;
spool ch.sql
select 'ttitle '''Chaining Summary for Table '||&&Enter_Table||''''
from dual 
/
select 'select substr(rowid,1,8)||substr(rowid,15,4) blk, sum (' blk,
       decode(column_id,1,'','+')||
             'nvl(vsize('||column_name||'),0)+1 ' rest 
>From user_tab_columns
where table_name=upper('&&Enter_Table')
order by column_id
/
select '+5)+100 summary  from '||upper('&&Enter_Table')||' group by substr(rowid,1,8)||substr(rowid,15,4)'
from dual
/
select 'having   sum (' blk,
       decode(column_id,1,'','+')||
             'nvl(vsize('||column_name||'),0)+1' rest 
from user_tab_columns
where table_name=upper('&&Enter_Table')
order by column_id
/
select '+5) +100   > 4096; ' from dual
/
spool off
Set feedback on  
@ch.sql
exit