How to find the High Water mark of the Oracle Table

Last updated on May 29th, 2019 at 05:38 pm

High Water Mark of Oracle table

High water mark is a term used in oracle table to show the blocks  which has been ever used in the life cycle of the oracle table. So basically if the oracle table block is flat structure, it is right most block in the structure.

This is used by Oracle process to read the data in the table in Full table scan.

For Manual Segment space Management, HWM is definite but for Automatic Segment space management, oracle maintained an HWM and a low HWM. Maintain two HWM as rows are not formatted like MSSM on increasing the High water Mark

The following process may be used at any time to determine the  high water mark of the oracle table

in an non ASSM tablespace (Manual segment space management)

set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;

begin
dbms_space.unused_space(
'table_owner',
'table_name',
'TABLE',
TOTAL_BLOCKS,
TOTAL_BYTES,
UNUSED_BLOCKS,
UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);

dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/

The following procedure may be used at any time to see the block allocations for a table stored in an ASSM tablespace(Automatic segment space management)

set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;

begin
dbms_space.space_usage (
'table_owner',
'table_name',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);

dbms_output.put_line('Unformatted Blocks                       = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                        = '||v_full_blocks);

end;
/

The above procedure was for Table, For table partition , we can use similar queries also

set serveroutput on

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;

begin
dbms_space.space_usage (
'table_owner',
'table_name',
'TABLE PARTITION',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes,

'PARTITION_NAME');

dbms_output.put_line('Unformatted Blocks                       = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                        = '||v_full_blocks);

end;
/

Once you know the HWM or free space, you may decide to shrink or rebuild the table. Here is the post which talks about how to rebuild the table

Leave a Reply