Oracle Table High Water Mark Report
This script displays the High Water Mark (HWM) for a specified table or for all tables in a schema. It uses the DBMS_SPACE.UNUSED_SPACE procedure to calculate total blocks, unused blocks, and the high water mark, helping DBAs analyze space usage and identify opportunities for table reorganization.
oraclepl-sqlstorage-tablespacesv1.0.0
0 stars0 downloads13 views0 comments
By OracleDba • Created
Code
(44 lines)1234567891011121314151617181920212223242526272829303132333435363738394041424344
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/high_water_mark.sql
-- Author : Tim Hall
-- Description : Displays the High Water Mark for the specified table, or all tables.
-- Requirements : Access to the Dbms_Space.
-- Call Syntax : @high_water_mark (table_name or all) (schema-name)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
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,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY ON