Search This Blog

Total Pageviews

Tuesday, 22 November 2011

Oracle High Water Mark for the specified table ( HWM )

 

High_Water_Mark.sql




SET SERVEROUTPUT ON
SET VERIFY OFF
undefine 1
undefine 2



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
undefine 1
undefine 2


SQL> /
Enter value for 1: EMP
Enter value for 2: SCOTT
TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK
------------------------------ --------------- --------------- ---------------
EMP 4 8 3

PL/SQL procedure successfully completed.

 

No comments:

Oracle DBA

anuj blog Archive