[Script] Tablespace Usage Alert

Hi,

This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts.

This will alert you via email if a certain tablespace  reaches the set threshold.

Let's share and make others love Oracle, too.

For tablespace monitoring alert scripts. We have the following:

Tablespace Monitoring Script

You will notice that there is a pattern in the scripts creation presented in this blog. 

Part 1: Report Generation : Here it is the tablespace report
Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report.
Part 3: Check if the file generates alert : Email is sent if entities met the threshold.


######----- Start of Script -----######

#!/bin/bash 
# Report tablespace usage 

sqlplus "/ as sysdba" << EOF

SET LINES 700
SET PAGESIZE 100
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET ECHO OFF
COL TABLESPACE_NAME FOR A30
SPOOL /tmp/tablespace.in

select 
a.tablespace_name, 
SUM(a.bytes)/1024/1024 "CurMB", 
SUM(decode(b.maxextend, 
null, 
A.BYTES/1024/1024, 
b.maxextend*8192/1024/1024)) "MaxMB", 
(SUM(a.bytes)/1024/1024 - (c."Free"/1024/1024)) "TotalUsed", 
(SUM(decode(b.maxextend, 
null, 
A.BYTES/1024/1024, 
b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree", 
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, 
null, 
A.BYTES/1024/1024, 
b.maxextend*8192/1024/1024)))) "UPercent" from dba_data_files a, 
sys.filext$ b, 
(SELECT d.tablespace_name ,  sum(nvl(c.bytes,0)) "Free" 
FROM dba_tablespaces d,DBA_FREE_SPACE c 
where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c 
where a.file_id = b.file#(+) and a.tablespace_name = c.tablespace_name 
GROUP by a.tablespace_name, c."Free"/1024 
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

SPOOL OFF
SET ECHO ON
SET FEEDBACK ON

exit;
EOF

cat /tmp/tablespace.in | sed '1,27d' | sed '$d' | sed '$d' >  /tmp/tablespace.out
rm -f /tmp/noreport
threshold="80" 
i=1
tbsresult=`cat /tmp/tablespace.out | awk '{ print $6 }' | sed 's/%//g'` 
for percent in $tbsresult; do 
if ((percent > threshold)) 
then
tbsusage=`cat /tmp/tablespace.out | head -$i | tail -1| awk '{print $1}'` 
echo "Warning: Tablespace ${tbsusage} is ${percent}% used." 
fi 
let i=$i+1 
done > /tmp/tablespace.warning

iFile=/tmp/tablespace.warning

if [[ -s $iFile ]] ; then
mailx -s "$ORACLE_SID on ${hostname}: Tablespace Warning" gab@lovesoracle.com < /tmp/tablespace.warning
else
echo "$iFile is empty." > /tmp/noreport
fi ;

######----- End of Script -----######

My other related monitoring scripts following this format can be accessed on the links below:

[Script] Simple Disk Space Monitoring Alert

[Script] ASM Diskgroup Space Usage Alert