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