High Water Mark management in Oracle Database

Description:

Each table is made up of extents and each extent is made up of oracle blocks – a common block size is 8k. So you have a table with 10 extents (80K).
When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.
When you delete the rows from table, the blocks below HWM may becomes empty but high water mark will stay as it is. Suppose you load the table with 1 million rows .Now you will have suppose the high water mark as 1 GB. Now if you delete all the 1 millions rows, then even the high water mark will be remain same as 1 GB. The only way to reduce the HWM is to rebuild the able or use truncate table. Oracle Truncate table reduce the High water mark.

Why do we need HWM ?

The high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity. Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object.
To resize any datafile to reclaim some space on the datafile. Need to check for the High water mark usage and based on that Water mark we may have to resize the respective datafile to get the space reclaimed.
We had an critical space issue on the datawarehouse environment to reclaim the space identified the datafiles using below query and resized the respective datafiles where we can get some space through this process.

Step1:Check the current data file size.

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 a40                 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

/

Step 2:Will suggest a new size based on HWM.

set lines 180 pages 200

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+100)||' '||'m'||';' 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);

Step 3:Find High Water Mark in particular Table.

SQL> 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;

/