Cheeky Cockatoo

set feedback off
set verify off
set echo off
set linesize 100
set pages 66
define db = &&1
define outfile = &&2

column table_name       format a30              heading "Table Name"
column num_rows		format 9,999,999,999	heading "No. Rows"
column bytes_col	format 9,999,999   	heading "Used|KBytes"
column empty_col	format 99,999    	heading "Empty|KBytes"
column ratio            format 999.9            heading "Pct|Free"
column avg_space	format 9,999	        heading "Avg|Free|Bytes/|Block"
column chain_cnt	format 999,999		heading "Chain|Count"
column avg_row_len	format 99,999		heading "Avg|Row|Len"

column ts noprint new_val ts_name
column today noprint new_value datevar
break on ts skip 2 page
ttitle left 'Analysis of Block Usage for: ' ts_name right datevar

spool &&outfile
select  tablespace_name ts,
        to_char(sysdate,'DD-MON-YYYY HH:MM') today,
	table_name,
	num_rows, 
	(blocks*2048)/1000 bytes_col, 
	(empty_blocks*2048)/1000 empty_col, 	
        decode(blocks,0,100,(empty_blocks/(empty_blocks+blocks))*100) ratio,
	avg_space, 	
	chain_cnt, 
	avg_row_len
from dba_tables 
where tablespace_name not like '%TEMP%' and 
      tablespace_name not like '%ROLLBACK%' 
and   num_rows is not null
order by tablespace_name,table_name;

spool off

exit