Oracle HIGH WATER MARK in Table

How to remove high water mark in table ?
remove HWM



HIGH WATER MARK
===============

HWM=dba_segments.blocks - DBA_TABLES.EMPTY_BLOCKS -1


in
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production



from scott


create table anuj_big_table1 (id number, name char(200));
insert into anuj_big_table1 select rownum,'a' from dual connect by rownum<100000;
delete from anuj_big_table1 where mod(id,10)<>0;
commit;


SQL> ANALYZE TABLE SCOTT.anuj_big_table1 COMPUTE STATISTICS;

Table analyzed.



connect sys or system


col Segment_name format a25
select s.SEGMENT_NAME Segment_name, s.blocks,t.EMPTY_BLOCKS,((s.blocks-t.EMPTY_BLOCKS) -1 ) HWM from dba_segments s ,DBA_TABLES t
where s.owner=t.owner
AND s.SEGMENT_NAME=t.table_name
and s.owner=t.owner
and t.table_name='ANUJ_BIG_TABLE1';

SEGMENT_NAME             BLOCKS             EMPTY_BLOCKS        HWM
-------------------------   ----------           ----------------        ----------
ANUJ_BIG_TABLE1            3072                 56                        3015

SQL> SQL> ALTER TABLE scott.ANUJ_BIG_TABLE1 ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE scott.ANUJ_BIG_TABLE1 SHRINK SPACE;

Table altered.


SQL> ALTER TABLE scott.ANUJ_BIG_TABLE1 SHRINK SPACE CASCADE;

Table altered.

SQL> ALTER TABLE scott.ANUJ_BIG_TABLE1 disable ROW MOVEMENT;

Table altered.

SQL> ANALYZE TABLE SCOTT.anuj_big_table1 COMPUTE STATISTICS;

Table analyzed.



SQL> ANALYZE TABLE SCOTT.anuj_big_table1 COMPUTE STATISTICS;

Table analyzed.



col Segment_name format a25
select s.SEGMENT_NAME Segment_name, s.blocks,t.EMPTY_BLOCKS,((s.blocks-t.EMPTY_BLOCKS) -1 ) HWM from dba_segments s ,DBA_TABLES t
where s.owner=t.owner
AND s.SEGMENT_NAME=t.table_name
and s.owner=t.owner
and t.table_name='ANUJ_BIG_TABLE1';

SEGMENT_NAME                  BLOCKS EMPTY_BLOCKS        HWM
------------------------- ---------- ------------ ----------
ANUJ_BIG_TABLE1                  312           16        295 <<<<<<-----

col Segment_name format a25
select s.SEGMENT_NAME Segment_name, s.blocks,t.EMPTY_BLOCKS,((s.blocks-t.EMPTY_BLOCKS) -1 ) HWM from dba_segments s ,DBA_TABLES t
where s.owner=t.owner
AND s.SEGMENT_NAME=t.table_name
and s.owner=t.owner
and s.owner='SCOTT'

SEGMENT_NAME                  BLOCKS EMPTY_BLOCKS        HWM
------------------------- ---------- ------------ ----------
DEPT                               8            0          7
SALGRADE                           8            0          7
EMP                                8            0          7
MYEMP                              8            0          7
ANUJ_BIG_TABLE                  3256            0       3255
ANUJ_BIG_TABLE1                  312           16        295
MYEMP_WORK                         8            0          7
MYEMP_WORK                         8            0          7
MYEMP_WORK                         8            0          7

9 rows selected.