HOW TO DOWN HIGH WATER MARK

This article will learn how to reset the High Water Mark in the oracle table. How does HWM work in the oracle table, what is the High Water Mark?

Table of Contents

What is a High Water Mark?

In the Oracle database, the data is stored in blocks, and by default one block size is 8KB so you have a table with 10 extents (80K).

When we create a table in oracle the HWM is at starting position. But during inserting some records in the table the High Water Mark keeps moving with table data.

For better understanding check the below image.

How to reset the High Water Mark

We have four ways to reset the High Water Mark listed below:

Move table to another tablespace
Export and Import
Truncate table and Insert
Analyze the table

Before doing the practical let's check the current DATAFILES, Redo and Controlfile size using the below command.

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Data File Report (all physical files)                       |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN tablespace      FORMAT a35                 HEADING 'Tablespace Name / File Class'

COLUMN filename        FORMAT a65                 HEADING 'Filename'

COLUMN filesize        FORMAT 9999999999999          HEADING 'File Size MB'

COLUMN autoextensible  FORMAT a4                  HEADING 'Auto'

COLUMN increment_by    FORMAT 999999999999          HEADING 'Next in MB'

COLUMN maxbytes        FORMAT 999999999999          HEADING 'Max Size MB'

BREAK ON report

COMPUTE sum OF filesize  ON report

COMPUTE sum OF maxbytes     ON    report

SELECT /*+ ordered */
   d.tablespace_name                     tablespace
 , d.file_name                           filename
 , d.bytes/1024/1024                      filesize
 , d.autoextensible                      autoextensible
 , (d.increment_by * e.value)/1024/1024  increment_by
 , d.maxbytes/1024/1024                  maxbytes
FROM
   sys.dba_data_files d
 , v$datafile v
 , (SELECT value
    FROM v$parameter
    WHERE name = 'db_block_size') e
WHERE
 (d.file_name = v.name)
UNION
SELECT
   d.tablespace_name   || ' **TEMP**'    tablespace
 , d.file_name                           filename
 , d.bytes/1024/1024                      filesize
 , d.autoextensible                      autoextensible
 , (d.increment_by * e.value)/1024/1024  increment_by
 , d.maxbytes/1024/1024                  maxbytes
FROM
   sys.dba_temp_files d
 , (SELECT value
    FROM v$parameter
    WHERE name = 'db_block_size') e
UNION
SELECT
   '[ ONLINE REDO LOG  ]'
 , a.member
 , b.bytes/1024/1024
 , null
 , TO_NUMBER(null)
 , TO_NUMBER(null)
FROM
   v$logfile a
 , v$log b
WHERE
   a.group# = b.group#
UNION
SELECT
  '[ STANDBY REDO LOG ]'
 , a.member
 , b.bytes/1024/1024
 , null
 , TO_NUMBER(null)
 , TO_NUMBER(null)
FROM
   v$logfile a
 , v$standby_log b
WHERE
  a.group# = b.group#
UNION
SELECT
   '[ CONTROL FILE     ]'
 , a.name
 , TO_NUMBER(null)
 , null
 , TO_NUMBER(null)
 , TO_NUMBER(null)
FROM
   v$controlfile a
ORDER BY 1,2
/

Output from above Query:

The below query suggest us the new size for datafiles as per High Water Mark.

set lines 180 pages 200
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+100)||' '||'m'||';' "Use the below Query" from ( select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Output from above Query:

Practice Environment

In this practice, we have one table named SHRI in SYS user as below.

SQL> desc shri

 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------
 EMPLOYEE_ID                                                                NUMBER(6)
 FIRST_NAME                                                                 VARCHAR2(20)
 LAST_NAME                                                         NOT NULL VARCHAR2(25)
 EMAIL                                                             NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                                               VARCHAR2(20)
 HIRE_DATE                                                         NOT NULL DATE
 JOB_ID                                                            NOT NULL VARCHAR2(10)
 SALARY                                                                     NUMBER(8,2)
 COMMISSION_PCT                                                             NUMBER(2,2)
 MANAGER_ID                                                                 NUMBER(6)
 DEPARTMENT_ID                                                              NUMBER(4)

Check tablespace for this table using the below command.

SQL> select owner,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "size in GB",TABLESPACE_NAME
  2  from dba_segments where OWNER='SYS' and SEGMENT_NAME='SHRI';

Output from the above query:

How to find High Water Mark in Table

The below query will help us to find High Water Mark in the table.

SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS

SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&Table_Name'),'ALL',a.table_name,Upper('&Table_Name'))
AND a.owner = Upper('&Table_Owner')
AND a.partitioned='NO'
AND a.logging='YES'
order by table_name;
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/

Output from the above query and you can see in the output right now there are zero unused blocks.

Create Unused blocks

Let's create some unused blocks in the table. In the below command, we deleted department_id 50,80, & 110.

SQL> delete from shri where DEPARTMENT_ID in (50,80,110);

21233664 rows deleted.

SQL>
SQL> commit;

Commit complete.

Check the High Water Mark after deleting the rows from the table.

Down High Water Mark

Let's down High WaterMark using the first method.

Solution 1. Move table to another tablespace

SQL>  alter table shri move tablespace users;

Table altered.

Solution 2. Export and Import

Take export of the particular table and import it again. if you have basic knowledge of oracle dba then you can easily perform the activity.

Solution 3. Truncate table and insert

This solution is a little risky, do it very carefully just create a duplicate table with the reference by the effective table.

SQL> create table shri2 as select * from shri;

SQL> drop table shri purge;

SQL> create table shri as select * from shri2;

Solution 4. Analyze the table

Oracle Segment Shrink Space option will consolidate the fragmented free space below the high watermark and compact the data. The high watermark will be moved back, and the reclaimed space will be released. After the shrink operation, data in the segment will be contained in a smaller number of blocks.

Oracle database Segment Shrink Space in the table and moves rows between existing blocks to compact the table data. So, before we attempt to shrink a table segment we need to enable row movement with the following command.

SQL> ALTER TABLE shri ENABLE ROW MOVEMENT;
SQL> ALTER TABLE shri SHRINK SPACE;

The DML operations are blocked till the High-water-mark is adjusted. Oracle suggested running shrink space in off-peak hours.

SQL> ALTER TABLE shri SHRINK SPACE COMPACT;

If the database is used 24x7 then shrink space compact to reset HWM. In that case, the progress of the shrink operation is saved in bitmap blocks of the corresponding segments.