Skip to main content

HWM (high watermark) of a tablespace ?

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculations, or better, based on the above SEGMENT theory, we should resize individual datafiles within a tablespace more accurately.
For the sake of simplicity & ease of understanding, we may still see the datafile usage as an overall tablespace usage :

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size';

--Tablespace-wise Used / unused blocks for HWM calculation

select tablespace_name, 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 a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
order by 1;


You may save this above data output as a snapshot of starting point. Now, let's create a script to resize individual datafiles (you may exclude some important ones, like SYSTEM, UNDO, TEMP). I've used 2 types of scripts - one is exact resizing to the HWM & other one is with some future space allocation planning built-in & still reducing wasted space :

--Exact resizing

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

-- Future Space planned resizing - change the 0.1, which is 10% estimate, to any other value less than 1, to alter how much extra you wish to still keep in each datafile / tablespace

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 + 0.1*(nvl(hwm,1)*8192)/1024/1024) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0
/


Dear reader, I've been able to make use of this procedure successfully. Please let me know your results & learnings.

Comments

Post a Comment

Please add your comment here

Popular posts from this blog

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning. Scenario: Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also. Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was : $ sqlplus ld.so.1: sqlplus: fatal: /app/oracle/product/10.2/lib/libsqlplus.so: Permission denied Killed The files under "lib" directory under home didn't have permission for anybody other than "oracle" user. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/libsqlplus.so When we permit the other users (other than oracle) to read/exe

Intereseting --- 249 days bug for Oracle on Linux servers

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited 1 Introduction Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267. Solution: This can be handled by applying the patch 4612267. The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems. For further information refer the Metalink Notes Note: 338461.1: SQL*Plus With Instant Client 10.2.0.1 Hangs, When System Uptime Is More Than 248 Days Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days 2 General Instructions Procedure to apply the patch 1. Do