REM script to report table statistics

rem
rem  NAME: tab_stat.sql
rem
rem  FUNCTION:Show table statistics for a user's tables or all tables.
rem
 set pages 56 lines 130 newpage 0 verify off echo off feedback off
rem
COLUMN owner            FORMAT a12            HEADING "Table Owner"
COLUMN table_name       FORMAT a17            HEADING "Table"
COLUMN tablespace_name  FORMAT a13            HEADING "Tablespace"
COLUMN num_rows         FORMAT 99,999,999     HEADING "Rows"
COLUMN blocks           FORMAT 99,999         HEADING "Blocks" 
COLUMN empty_blocks     FORMAT 99,999         HEADING "Empties" 
COLUMN space_full       FORMAT 999.99         HEADING "% Full" 
COLUMN chain_cnt        FORMAT 99,999         HEADING "Chains"
COLUMN avg_row_len      FORMAT 9,999,999 HEADING "Avg|Length|(Bytes)"
COLUMN num_freelist_blocks FORMAT 99,999 HEADING "Num|Freelist|Blocks"
COLUMN avg_space_freelist_blocks FORMAT 99,999 HEADING "Avg|Space|Freelist Blocks"
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)
   * 2048)) space_full,
   chain_cnt, avg_row_len,avg_space_freelist_blocks, 
   num_freelist_blocks
FROM 
   Remote DBA_tables
WHERE 
  owner NOT IN ('SYS','SYSTEM')
UNION
SELECT 
   owner, table_name, tablespace_name, 
   num_rows, blocks,empty_blocks,  
   100*((num_rows * avg_row_len)/((GREATEST(blocks,1) + empty_blocks)
   * 2048)) space_full,
   chain_cnt, avg_row_len,avg_space_freelist_blocks, 
   num_freelist_blocks
FROM 
   Remote DBA_object_tables
WHERE 
  owner NOT IN ('SYS','SYSTEM')
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

