The high-water mark is the level at which blocks have never been formatted to receive data.
Prior to 10g, only option to reset HWM (High Water Mark) was to either truncate data and re-insert or export import.
With 10g, there is an option that can be used to reset HWM on a table using "
shrink space".
eg.,
alter table
table_name shrink space; --can be used cascade along with it (Read the manual)
Here is an example showing how HWM takes place and how this option helps to reset it back:
create table hm_test as select *from all_tables where 1=2;
select blocks, extents
from dba_segments
where segment_name='HM_TEST';
--no records (
read this to see why)
insert into hm_test select *from all_tables where rownum < 2;
commit;
select blocks, extents
from dba_segments
where segment_name='HM_TEST';
--blocks extents
--8 1
analyze table hm_test compute statistics;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--Querying dba_tables for 'Blocks' and 'Empty_blocks' should give the high water mark.
--
--Blocks -- > Number blocks that has been formatted to recieve data
--Empty_blocks ---> Among the allocated blocks, the blocks that were never used
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--5 3 1
insert into hm_test select *from all_tables;
commit;
select blocks, extents
from dba_segments
where segment_name='HM_TEST';
--blocks extents
--88 11
analyze table hm_test compute statistics;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 2285
--now delete the records to see if empty_blocks number changes
delete hm_test;
commit;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 2285
analyze table hm_test compute statistics;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 0
--as you see empty_blocks is still 8
--To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.
select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from hm_test;
--0
--This works fine if only one file is used for the segment.
--If more files, we need to include the file number in some way, for instance:
select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from hm_test;
--o
--resetting HWM
alter table hm_test shrink space compact cascade;
--ORA-10636: ROW MOVEMENT is not enabled
alter table hm_test enable row movement;
alter table hm_test shrink space compact cascade;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 0
analyze table hm_test compute statistics;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 0
--still no change
--now lets to shrink space cascade
alter table hm_test shrink space cascade;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--80 8 0
analyze table hm_test compute statistics;
select blocks,empty_blocks,num_rows from dba_tables where table_name='HM_TEST';
--BLOCKS EMPTY_BLOCKS NUM_ROWS
--1
7 0
