44 lines
1.5 KiB
SQL
44 lines
1.5 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- 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 |