1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

Tablespace Monitoring and Alerting Script

Tuesday, 31 July 2012 11:08

Written by Prakash Palani

Print E-mail


Script to Monitor Oracle Tablespace

Here is the script that can help you monitor the tablespace and alert when the threshold value is reached, it involves three different (Oracle, MS-DOS and VBScript) development techniques. Once you are done with the creation of the script, you may use the windows scheduler to run the script at a pre-defined interval. Make sure that you are able to connect to the database from the pc/server where you are planning to run all these scripts.

Script 1 : Getting the Tablespace Size Details

File Name : tbspstat.sql



set pages 60

column  pct_used format 999.9       heading "Used"


column  ts_name    format a21      heading "Tablespace Name"

break   on report

spool d:\alert\TbspStat.txt


select    nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name


,               ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100   pct_used

from      (select  sum(bytes)/1024                                             Kbytes_free

,                               max(bytes)/1024                             largest

,                               tablespace_name

from                      dba_free_space

group by              tablespace_name)                                          a

,               (select                  sum(bytes)/1024                             Kbytes_alloc

,                               tablespace_name

from                      dba_data_files

group by              tablespace_name)                                          b

where   a.tablespace_name (+) = b.tablespace_name

order by 2,1;


spool off




Script # 2 : Call the Oracle SQL Script from MS-DOS Batch file

FileName : tbspstatmain.bat


del d:\alert\tbspstat.txt

sqlplus "/ @D01 as sysdba" @TbSpStat.sql




Script 3 : Call the  VBScript to trigger emails

FileName : TbSpMonitor.vbs


Option Explicit


Dim objFSO, strTextFile, strData, strLine, arrLines,i

CONST ForReading = 1



strTextFile = "TbSpStat.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll

arrLines = Split(strData,vbCrLf)


For Each strLine in arrLines


if trim(Right(strLine,len("                                                    ")+4)) "" and i > 3  then

if len(trim(Right(strLine,len("                                                    ")+4))) = 4 then

if trim(Right(strLine,len("                                                    ")+4)) > 90 then

Dim BodyText

BodyText = "Alert - - - Tablespace - Used % Reached Threshold Value - " & trim(strLine) & " %"

Dim objMessage

Set objMessage = CreateObject("CDO.Message")

objMessage.Subject = BodyText

objMessage.From = " This e-mail address is being protected from spambots. You need JavaScript enabled to view it "

objMessage.To = " This e-mail address is being protected from spambots. You need JavaScript enabled to view it "

objMessage.TextBody = "Please investigate further, add datafile if needed"


'SMTP Configuration

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2


'Name or IP of Remote SMTP Server

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ""


'Server port (typically 25)

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25




'End of SMTP configuration


end if

end if

end if



Set objFSO = Nothing


{fcomment} {flike}




Tablespace Monitoring and Alerting Script