/* ckhwm.sql
    check the highwater mark for a table
    Will check a table's highwater mark. This info is userful to allow you 
    to see how full a table is getting based on the highwater mark compared
    to the allocated blocks. It does not tell you how full each block is.
    It also assumes dba*_table access and that statistics have been run
    on the table.
*/
accept tabowner char prompt 'Who is the table owner : ';
accept tabname char prompt 'What is the table name : ';
prompt Calculating the high water mark.....
set pause off
set feedback off
set verify off
col pctfull format 999.99
select s.extents, s.blocks, t.empty_blocks,
       s.blocks - t.empty_blocks - 1 tabhwm,
       ((s.blocks - t.empty_blocks - 1) / s.blocks) * 100 pctfull
  from dba_tables t, dba_segments s
 where t.table_name = upper('&&tabname')
   and t.owner = upper('&&tabowner')
   and s.segment_name = t.table_name
   and s.owner = t.owner
   and s.segment_type = 'TABLE';