REM Script to report additional table statistics

rem
rem  NAME: tab_stat.sql
rem
rem  FUNCTION:  Show table statistics for user's tables or all tables.
rem  10/08/01 Updated for 9i Mike Ault
rem
 SET PAGES 56 LINES 132 NEWPAGE 0 VERIFY OFF ECHO OFF feedback OFF
rem
COLUMN owner            FORMAT a12            HEADING "Table Owner"
COLUMN table_name       FORMAT a20            HEADING "Table"
COLUMN tablespace_name  FORMAT a20            HEADING "Tablespace"
COLUMN num_rows         FORMAT 999,999,999    HEADING "Rows"
COLUMN blocks           FORMAT 999,999        HEADING "Blocks" 
COLUMN empty_blocks     FORMAT 999,999        HEADING "Empties" 
COLUMN space_full       FORMAT 999.99         HEADING "% Full" 
COLUMN chain_cnt        FORMAT 999,999        HEADING "Chains"
COLUMN avg_row_len      FORMAT 99,999,999 HEADING "Avg Length|(Bytes)"
rem
START title132 "Table Statistics Report"
DEFINE OUTPUT = 'rep_out\&db\tab_stat..lis'
SPOOL &output
rem
BREAK ON OWNER SKIP 2 ON TABLESPACE_NAME SKIP 1;
SELECT owner, table_name, tablespace_name, num_rows, blocks,
     empty_blocks,  
     100*((num_rows * 
     avg_row_len)/((GREATEST(blocks,1)+empty_blocks)*value))    
     space_full,
     chain_cnt, avg_row_len
FROM Remote DBA_tables, v$parameter
WHERE OWNER NOT IN ('SYS','SYSTEM')
and num_rows>0
and name='db_block_size'
ORDER BY owner, tablespace_name;
SPOOL OFF
PAUSE Press enter to continue
SET PAGES 22 LINES 80 NEWPAGE 1 VERIFY ON feedback ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF