REM ########################################################################
REM ##   Author  : Sunil Kumar
REM ##             Senior DBA
REM ##   Email   : sunilagarwal@hotmail.com
REM ##             sunil.kumar@thehartford.com
REM ##
REM ##   Script to determine highwater mark of tables. It is essential
REM ##   to run ANALYZE_SCHEMA utility or ANALYZE TABLE commands before
REM ##   running this script for accurate statistics.
REM ##
REM ##   It displays all the tables whose high water mark <> no# of used
REM ##   blocks.
REM ##
REM ##   How To Run:
REM ##           SQL>@highwtr
REM ##   It will ask for the owner (press enter for all owners) and
REM ##   the table name (press enter for all tables).
REM ##
REM ########################################################################

set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page

select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    and a.owner not in('SYS','SYSTEM')
    and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner like upper('&owner')||'%'
    and a.table_name like upper('&table_name')||'%'
order by 1,2
/

set verify on
clear columns
clear breaks