Oracle recovery of high water mark

Select round ((1-a.used/b.num_total) * 100Pol 0) percent from

(SELECT COUNT (DISTINCT SUBSTR (rowid,1,15)) Used FROM table name) a

(select blocks num_total from dba_tables where table_name=' table name 'and wner=' username') b

By comparing the blocks field in dba_tables with the field with data, using 1 minus to calculate the percentage of blocks without data (generally, if it exceeds 20%, it needs to be recycled) to determine whether the high water mark needs to be reclaimed.

SQL > alter table test_shrik enable row movement

SQL > alter table test_shrik shrink space

SQL > select t.tableroomname memorialBLOCKS MagiceEMPTYBLOCKSreNUMachirows

From user_tables t

Where table_name = upper ('test_shrik')

Exec dbms_stats.gather_table_stats ('sys','test_shrik')

SQL > select t.tableroomname memorialBLOCKS MagiceEMPTYBLOCKSreNUMachirows

From user_tables t

Where table_name = upper ('test_shrik')

The high water line has dropped.