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;

exit

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

i=0

 

strTextFile = "TbSpStat.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

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

arrLines = Split(strData,vbCrLf)

 

For Each strLine in arrLines

i=i+1

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

 

objMessage.Configuration.Fields.Update

 

'End of SMTP configuration

objMessage.Send

end if

end if

end if

Next

wscript.quit

Set objFSO = Nothing

 

{fcomment} {flike}

Share

 

 

Tablespace Monitoring and Alerting Script