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;
/