Check tablespace and send email

Below is script to check tablespace and send email if tablespace reaches above 90%

#!/bin/ksh
. $HOME/.profile
cat /u2/scripts/tablespace.log
sqlplus “/ as sysdba” << EOF
set feedback off echo off
set linesize 100
set pagesize 200
@/u2/scripts/cmstablespace.sql
spool off
exit
EOF
if [ `cat /u2/scripts/tablespace.log|wc -l` -gt 0 ]
then
cat  /u2/scripts/tablespace.log >> /u2/scripts/mailcontent
cat /u2/scripts/tablespace.log |mailx -s “PRODLIVE() : Tablespace with less than 10% free space”  john.doe@company.com
rm -rf /u2/scripts/tablespace.log
rm -rf /u2/scripts/mailcontent
fi

 

cmstablespace.sql

—-SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
–select name from v$database;
Spool /u2/scripts/tablespace.log
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED(MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE(MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL(MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE
FROM   (
SELECT       TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off

5 thoughts on “Check tablespace and send email

  1. Hi Mohammad,

    I had prepared script,but getting confusion.

    #!/bin/ksh
    #cat /export/home/wmuser/Darshan/tablespace.alert
    sqlplus webm/WEBM@devwbm
    set feedback off echo off
    set linesize 100
    set pagesize 200
    @/export/home/wmuser/Darshan/Tablespace.sql
    spool off
    exit
    then
    cat tablespace.alert -l tablespace.alert > tablespace.tmp
    echo mail -s “TABLESPACE ALERT for webMethods” darshan.sv@ge.com /export/home/wmuser/Darshan/tablespace.alert< tablespace.tmp
    echo "Done"

    in 2nd line i just changed .log to .alert.
    this tablespace.alert file created under that path,when i running manually just script is just opening Tablespace.alert file,and showing same output even if i runs that in different times.suggest me.

    Like

  2. Hello Mohammad, thanks for sharing this knowledge. The Shell script could be put in a Cron job to send you email periodically. But, what if you more than 20 databases, and you only want to receive alerts emails for only those databases instances that the Tablespace size reaches 90%. How would you included that in Shell script. Please send an email directly to my email if you can.

    thanks,
    Kabbo

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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