HIGH WATER USAGE (HWM USAGE)

##############################
HIGH WATER USAGE (HWM USAGE)
##############################

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 dataware house 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;
                                                              Smallest
                                                                  Size   Current     Poss.
FILE_NAME                                                        Poss.      Size   Savings
------------------------------------------------------------ --------- --------- ---------
+DATA_PRD/PROD/datafile/users_data.363.825564829          31,396    31,744       348
+DATA_PRD/PROD/datafile/users_data.1042.866689707         16,076    16,512       436
                                                                                 ---------
sum                                                                                    784