High Water Mark USAGE

Before start this article we must know about “HWM” . After did lots of R&D i found the technical definition

Explanation in Detail: (http://www.dba-oracle.com/t_high_water_mark.html) .

The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents.

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.

Note: This is for a temporary Fix and have to plan for a better storage.

set verify off
column file_name format a60 word_wrapped
column smallest format 9999,990 heading "Smallest|Size|Poss."
column currsize format 9999,990 heading "Current|Size"
column savings format 9999,990 heading "Poss.|Savings"
set pages 100
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
SELECT FILE_NAME, CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL((NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS
where tablespace_name ='USERS' GROUP BY FILE_ID ) DBAFS
WHERE DBADF.TABLESPACE_NAME='USERS' and DBADF.FILE_ID = DBAFS.FILE_ID(+) and
(CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )) > 300;

FILE_NAME Poss. Size Savings
--------------------------------------------------------------------------------- --------- --------- ---------
+DATA_PRD_1/PROD/datafile/users_data.363.82556482999 31,396 31,744 348
+DATA_PRD_1/PROD/datafile/users_data.1042.86668970799 16,076 16,512 436
                                                                   ---------
                                                                    sum 784

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp