-- #############################################################################################
--
-- %Purpose: Compare HW-Mark which is say 20% larger than the actual data in the tables (Ora8)
--
-- This script lists all of the tables specified by owner,
-- where the High Water Mark is say 20% larger than the actual
-- data in the tables.
-- This will indicate which tables require a rebuild.
--
-- Notes:
--
-- This script generates another script(hwm_get_sql.lst), which 
-- it calls at the end.  The hwm_get_sql.lst file is NOT deleted 
-- when it is finished.
-- You do NOT need to run an analyze on the tables before running
-- this script
--
-- The ROWID format changed between Oracle 7 and Oracle 8.
--
--                   ################################
--                   THIS SCRIPT IS FOR ORACLE 8 ONLY
--                   ################################
--
-- Author: John Dixon, published on http://www.revealnet.com
--
-- #############################################################################################
--
set echo off 
set heading off 
set pagesize 0 
set feedback off
set linesize 1000
set trimspool on
set wrap on
set verify off
rem Get the variables
accept table_owner char prompt 'Enter the table owner: '
accept percentage_larger number default 20 prompt 'Enter the percentage larger threshold the HWM can be [default 20]: '
prompt This may take a while...
rem 
set termout off
rem 
spool hwm_get_sql.lst
prompt set echo off 
prompt set heading off
prompt set termout on
prompt set verify off

rem Step 1 - First generate the script to calc hwm and data for each table in schema.

select 'select owner,segment_name,hwm,actual_data from'||chr(10)||
' (SELECT round((COUNT(DISTINCT '||chr(10)||
'SUBSTR(rowid,1,15))+1)*'||vp.value/1024||'/1024) as Actual_Data FROM '||chr(10)||
ds.owner||'.'||ds.segment_name||') ad,'||chr(10)||
'(select s.owner,s.segment_name,round(s.BLOCKS*'||vp.value/1024||'/1024) as HWM '||chr(10)||
'from dba_segments s, dba_tables t where s.owner='''||ds.owner||''' '||chr(10)||
'and s.segment_name='''||ds.segment_name||''' and t.owner=s.owner and t.table_name=s.segment_name) hw'||chr(10)||
' where hw.hwm>(ad.actual_data*&&percentage_larger/100)+ad.actual_data'||
' and ad.actual_data >0;' 
from dba_segments ds, dba_tables dt, v$parameter vp 
where ds.owner=upper('&&Table_Owner') and
ds.segment_name=dt.table_name and 
ds.owner=dt.owner 
and vp.name='db_block_size'
order by segment_name 
/ 
spool off 

rem STEP 2 - Now Generate the output

rem spool hwm.lst
set termout on
column Owner format A10 heading Owner 
column segment_name format a40
column hwm format 999,999,999
column actual_data format 999,999,999

prompt High Water Mark Report - This will indicate tables that require rebuilding.
prompt Owner      Table                                         HWM(Mb)     Data(Mb)
start hwm_get_sql.lst
REM spool off  (hwm.lst)