Identify a High Water Mark

High Water Mark (HWM) might cause a problem in terms of space-wasting. It is essential to regularly check the database for suspicious space growth. Check out the scripts below!

SELECT dts.owner
      ,dts.table_name
      ,dsse.partition_name
      ,dsse.segment_type
      ,dts.blocks blks_used
      ,dts.avg_space
      ,dts.num_rows
      ,dts.avg_row_len
      ,dts.empty_blocks empty_blks
      ,to_char(dts.avg_row_len * dts.num_rows, '999,999,999') AS est_blks
      ,to_char(dsse.blocks, '999,999,999') alloc_blks
      ,(greatest(dts.blocks, 1) / greatest(dsse.blocks, 1)) * 100 pct_hwm
      ,dts.num_rows * dts.avg_row_len data_in_bytes
      ,(dts.num_rows * dts.avg_row_len) / 8192 data_in_blks
      ,((dts.num_rows * dts.avg_row_len) / 8192) * 1.25 mod_data_in_blks
      ,(((dts.num_rows * dts.avg_row_len) / 8192) * 1.25) / dsse.blocks pct_spc_used
FROM dba_tab_statistics dts
JOIN dba_segments dsse
ON dts.table_name = dsse.segment_name
WHERE 1 = 1
AND dsse.segment_type = 'TABLE'
AND dts.table_name = 'SOME_TABLE'
AND ((dts.avg_row_len * dts.num_rows) * 1.5) < dsse.blocks
AND dts.owner = '%%'
ORDER BY table_name
        ,pct_hwm

Another way to check it is to use the system package  DBMS_SPACE.UNUSED_SPACE (extra privileges needed).

DECLARE
  CURSOR cu_tables IS
    SELECT a.owner
          ,a.table_name
    FROM dba_tables a
    WHERE 1 = 1
    AND a.table_name = 'TABLE_A'
    AND a.owner = 'JOHN_DOE'
    AND a.partitioned = 'NO'
    AND a.logging = 'YES';

  op1 NUMBER;
  op2 NUMBER;
  op3 NUMBER;
  op4 NUMBER;
  op5 NUMBER;
  op6 NUMBER;
  op7 NUMBER;
  
  v_block_sz v$log.blocksize%TYPE;
  
BEGIN

  SELECT blocksize INTO v_block_sz FROM v$log WHERE status = 'CURRENT';

  dbms_output.disable;
  dbms_output.enable(1000000);
  dbms_output.put_line(rpad('TABLE',29,' ')|| rpad(' UNUSED_BLOCKS',15,' ')||rpad(' UNUSED_BYTES',15,' ')|| rpad(' TOTAL_BLOCKS',15,' ')|| rpad(' HWM',15,' '));
  dbms_output.put_line(rpad('-',80,'-'));
  FOR cur_rec IN cu_tables
  LOOP
    dbms_space.unused_space(cur_rec.owner, cur_rec.table_name, 'TABLE', op1, op2, op3, op4, op5, op6, op7);
    dbms_output.put_line(rpad(cur_rec.table_name, 30, ' ') ||
                         rpad(op3, 15, ' ') || rpad(op3 * v_block_sz, 15, ' ') ||
                         rpad(op1, 15, ' ') ||
                         rpad(trunc(op1 - op3 - 1), 15, ' '));
  END LOOP;
END;