prompt chaining.sql
set linesize 132
set pagesize 999
VARIABLE total_disk_reads  NUMBER;
VARIABLE total_buffer_gets NUMBER;
VARIABLE total_parse_calls NUMBER;
VARIABLE service_time      NUMBER;
VARIABLE wait_time         NUMBER;
VARIABLE total             NUMBER;
VARIABLE total_phyrds      NUMBER;
VARIABLE total_phywrts     NUMBER;
VARIABLE vers              VARCHAR2(80);
VARIABLE sqlstat           VARCHAR2(600);

Rem set pagesize 24
set feedback off
set serveroutput off
set heading off

prompt Chained Rows 
REM Barb modified to add some colulmns
prompt Note that this information relies on analyzed data, so if you haven't 
prompt analyzed tables lately, the data will be out of date
select substr(owner || '.' || table_name,1,30) "Table",chain_cnt,num_rows,blocks*8192 "Bytes",pct_free
 from sys.dba_tables where chain_cnt > 0 order by chain_cnt,owner,table_name;
select sum(chain_cnt) from sys.dba_tables;
prompt
spool chaining.lis
prompt Chained rows increases I/O operations
prompt *************************************************************************
prompt
prompt Next extent too large in tablespace
select tablespace_name TABLESPACE,  
       table_name      TABLE_NAME, 
       next_extent     NEXT  
from   user_tables     OUTER  
where not exists (select 'X' from sys.dba_free_space INNER  
where  OUTER.tablespace_name = INNER.tablespace_name and bytes >= next_extent); 
prompt *************************************************************************
spool off
spool bigtables.lis
REM What are the biggest tables?
select substr(owner,1,10),substr(segment_name,1,40),tablespace_name,bytes from sys.dba_segments
where owner not like ('SYS%') and segment_type='TABLE' and bytes > 200000000 order by bytes
spool off
exit