Ad-hoc tips (High Water Mark)

Posted on July 16, 2008. Filed under: DBA/Admin Tips |

This is another tip which i used recently.

Whenever, we get alerted about a tablespace reaching threshold levels, our immediate action would be to add a datafile. In some cases, we can resize/shrink the datafiles of the tablespaces to gain space. This can be done by knowing the high-water mark of the datafile.

Metalink Note : 130866.1 has a script that can be run against a database to know HWM of ALL datafiles of ALL tablespaces.

Note: Edit the script to change “binary_integer” declaration to “number”.

Output will be like below

Tablespace:  BIGPARTYWEEK1 Datafile: /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf
Can be resized uptil: 49152 Bytes, Actual size: 1040384
Tablespace:  IFS_LOB_N Datafile: /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf
Can not be resized, no free space at end of file.

I ran this script against my Apps database and did shrink few datafiles
from SYSTEM and other tablespaces.

PS: Since this calculates for ALL datafiles, it took around 5 mts
in my 11i10 instance.

Hope this tip was useful.

OraclePitStop Team

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

One Response to “Ad-hoc tips (High Water Mark)”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

But what is the threshold value for HWM in case of tables when you should take initiative to reorg? Any reference?


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: