REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_tab_chain_row.sql
REM
REM <SQLDIR_GRP>TAB</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script will show the RowIDs for all chained rows
REM	this will allow you to quickly see how much of a problem chaining
REM	is in each table. If chaining is prevalent in a table, then that table
REM	should be rebuild with a higher value for PCTFREE
REM	--	
REM	PCTFREE - tells the database how much space should be kept FREE IN EACH
REM	          DATA BLOCK. This space is then used when rows that are already
REM	stored in the data block extent in length via UPDATEs.
REM	--
REM	Before running this script you need to analyze all tables in the database
REM	to detect and record chained rows in tables:
REM	--
REM	SQL> analyze table <table_name> list chained rows into CHAINED_ROWS;
REM	--
REM	CHAINED_ROWS table must be created before "analyze"
REM	/rdbms/admin/UTLCHAIN.SQL to create it
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_chain_row.sql
REM 
REM Example:
REM	s_tab_chain_row.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set term on

prompt FIRST CREATE table CHAINED_ROWS ...
prompt  

prompt  
prompt Generating dynamic SQL to analyze all tables for existance of chained rows
prompt This process will take some time
prompt

set term off

spool chain.tmp

set lines 132
set pages 0

select 'analyze table '||owner||'.'||table_name||' list chained rows into CHAINED_ROWS;'
from dba_tables
/

spool off

set term on

prompt Running analization script ...
prompt After it's done you'll see information on the chained rows
prompt  

set term off

@chain.tmp




set term on

clear breaks
set lines 132
set pages 90
col owner_name format a15


-- select		owner_name	/*Owner of the data segment*/
-- ,		table_name	/*Name of the table with the chained rows*/
-- ,		cluster_name	/*Name of the cluster, if it is clustered*/
-- ,		head_rowid	/*RowID of the first part of the row*/
-- from		chained_rows
-- /

break on owner_name skip 1
compute count of table_name on owner_name

select		owner_name			/*Owner of the data segment*/
,		table_name			/*Name of the table with the chained rows*/
,		cluster_name			/*Name of the cluster, if it is clustered*/
,		count(head_rowid) row_count	/*Count of RowID of the first part of the row*/
from		chained_rows
group by	owner_name
,		table_name
,		cluster_name
/

set term off
host del chain.tmp
set term on