Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Tuesday, January 17, 2012

Checking Segment High Water Mark (HWM) in Oracle 10g

Print Friendly and PDF
The High Water Mark is equal to “Total Blocks” - “Unused Blocks”.

Substitute “<<<A USER>>>” and “<<<A TABLE>>>” with the real values.
Thanks to this post here.

set serveroutput on size 100000;
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name = '<<<A TABLE>>>') LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => '<<<A USER>>>' ,
segment_name => c1_row.table_name ,
segment_type => 'TABLE' ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( 'Data for '|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD('*',LENGTH(c1_row.table_name) + 10,'*'));
DBMS_OUTPUT.PUT_LINE( 'Total Blocks.................'|| alc_bks );
DBMS_OUTPUT.PUT_LINE( 'Total Bytes..................'|| alc_bts );
DBMS_OUTPUT.PUT_LINE( 'Unused Blocks................'|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( 'Unused Bytes.................'|| unsd_bts );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext File Id........'|| luefi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext Block Id.......'|| luebi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Block..............'|| lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

No comments: