Script to monitor tablespace usage and send alert

This script can be used to connect to database, check its tablesapce at regular intervals and send email if it finds any tablespace usage > the threshold.

#! /usr/local/bin/perl 

use POSIX;
use Time::localtime;

################################################################################
## TABLESPACE_NAME, TOTAL_GB, USED_GB, FREE_GB, PCT_USED.
################################################################################
while(1){
my $SQL = "init_tb_pct.sql";
system("rm init_tb_pct.sql");
#change this loc to your dir and ensure it is writable
my $report_loc = "/home/luhuang/public_html/upload/STQA_TB_Report";
my $datestr = GetDate();
my $filename = "STQA_TB_Reprt_" . "$datestr" . ".txt";
my $REPORT = "$report_loc" . "/" . "$filename";

my $log = "I_am_running.log";
system("echo 0 >> $log");

open (SQLS, ">$SQL");
print SQLS "set feedback off\n";
print SQLS "set pages 1000\n";
print SQLS "spool $REPORT \n";
print SQLS "select  a.tablespace_name Tablespace_Name,(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id))/1024/1024/1024  Total_GB,(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id) -  sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024 Used_GB,(sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024  Free_GB,100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) - (sum(a.bytes)/count( distinct b.file_id ) )) / (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id)) PCT_USED from sys.dba_free_space a, sys.dba_data_files b where a.tablespace_name = b.tablespace_name group by a.tablespace_name, b.tablespace_name;\n";
print SQLS "spool off\n";
print SQLS "/\n";
print SQLS "exit;\n";
close SQLS ;
#update your connection string here
my $cmd = "sqlplus system/xxx\@'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = slcxxx.us.xxxx.com)(PORT = 1520)))(CONNECT_DATA =(SERVICE_NAME = xxfdb )(INSTANCE_NAME = xxxb )))' \@$SQL";
system("$cmd");

open (SQLS, "$REPORT");
my @lines = <SQLS>;
my $count = 0;
my $trigger = 0;
foreach $line (@lines) {
	if ($count > 1){
		$line =~ s/^\s+|\s+$//g;
		if (length($line) > 2) {
			my ($TABLESPACE_NAME, $TOTAL_GB, $USED_GB, $FREE_GB, $PCT_USED) = split(/\s+/, $line);
			if (($PCT_USED > 90) && ($trigger < 1)) {
				send_mail($filename);
				$trigger = 1;
			}
		}
	}
	$count = $count + 1;
}

close SQLS ;
if($trigger eq 0){
	system("rm $REPORT");
}
sleep 900;
}

sub GetDate {
    my $tm                   = localtime;
    my ($year, $month, $day) = ($tm->year+1900, ($tm->mon)+1, $tm->mday);
    my ($hour, $min, $sec)   = ($tm->hour, $tm->min, $tm->sec);

    my $date = sprintf("%4s%2s%2s", ${year},${month}, ${day});
    my $time = sprintf("[%2s:%2s:%2s]", ${hour}, ${min}, ${sec});
    my $date_time = sprintf("%4s%2s%2s%2s%2s%2s", ${year},${month}, ${day}, ${hour}, ${min}, ${sec});
    $date =~ s/ /0/g;
    $time =~ s/ /0/g;
    $date_time =~ s/ /0/g;

    return $date_time;
}

sub send_mail
{
(my $filename) = @_;
$to1="luohua.huang\@gmail.com";
open(MAIL,'|/usr/lib/sendmail -t -f $FROM');
print MAIL "Mime-Version: 1.0\n";
print MAIL "Content-type: text/html\; charset=\"us-ascii\"\n";
print (MAIL "To: ${to1} \r\n");
print MAIL "Subject: <Action Required> Tablesapce Report \n";
print MAIL "Customize your report here";
close (MAIL);

}