Create a folder inside the Local Disk D named scripts and create the PowerShell script alert_tbs.pl1 to check send email if tbs_check_warn.txt is greater than 2kb
# Script to check tablespace space. Sends an email if a tablespace is >95%.
# It needs a connection to the database with access to the dba_tablespaces and dba_freespace views.
# $env:PATH+=";D:\app\oracle\product\12.2.0\dbhome_1\network\admin"
cd D:\scripts
Remove-Item -Path "D:\scripts\logs\tbs_check_warn.txt"D:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus -s 'username/password@SID''@D:\scripts\tbs_check_warn.sql'| out-file 'D:\scripts\logs\tbs_check_warn.txt'if($(get-item -path D:\scripts\logs\tbs_check_warn.txt).length -gt 2){invoke-expression -Command .\sendmail_tablespace_warn.ps1}
Create another script in the same folder named tbs_check_warn.sql to check for tablesace.
set feed off
set lines 120 pages 1000
col tablespace_name format a20
col allocated format 9999990.99
col freespace format 9999990.99
col maxsize format 9999990.99
col pctused format 90.99
select tablespace_name, allocated, freespace,(allocated-freespace)/maxsize*100as pctused, maxsize
from(
select a.tablespace_name,sum(a.bytes)/1024/1024as allocated,(
select nvl(sum(b.bytes)/1024/1024,0)from dba_free_space b
where b.tablespace_name = a.tablespace_name
)as freespace,sum(decode(a.maxbytes,0,a.bytes,a.maxbytes))/1024/1024as maxsize
from dba_data_files a
group by a.tablespace_name
)where(allocated-freespace)/maxsize*100>95
order by 1;
exit
Lastly, create a PowerShell script to send email named as sendmail_tablespace_warn.ps1
# Script to send a mail if a tablespace is >95% Used. Called by tablespace_check.ps1
#
$EmailFrom ="hostname@client.com.au"
$EmailTo ="someone@somewhere.com.au"
$Subject ="CLIENT:PROD HOST:SID:Tablespace Space:WARNING"
$Body ="A Tablespace in SID on the PROD Host is over 80% full"
$SMTPServer ="client.smtp.com.au"
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer,25)
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username","password");
$SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)
Create a folder inside the Local Disk D named scripts and create the PowerShell script alert_tbs.pl1 to check send email if tbs_check_warn.txt is greater than 2kb
# Script to check tablespace space. Sends an email if a tablespace is > 95%. # It needs a connection to the database with access to the dba_tablespaces and dba_freespace views. # $env:PATH += ";D:\app\oracle\product\12.2.0\dbhome_1\network\admin" cd D:\scripts Remove-Item -Path "D:\scripts\logs\tbs_check_warn.txt" D:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus -s 'username/password@SID' '@D:\scripts\tbs_check_warn.sql' | out-file 'D:\scripts\logs\tbs_check_warn.txt' if ($(get-item -path D:\scripts\logs\tbs_check_warn.txt).length -gt 2) {invoke-expression -Command .\sendmail_tablespace_warn.ps1}
Create another script in the same folder named tbs_check_warn.sql to check for tablesace.
set feed off set lines 120 pages 1000 col tablespace_name format a20 col allocated format 9999990.99 col freespace format 9999990.99 col maxsize format 9999990.99 col pctused format 90.99 select tablespace_name, allocated, freespace, (allocated-freespace)/maxsize*100 as pctused, maxsize from ( select a.tablespace_name, sum(a.bytes)/1024/1024 as allocated, ( select nvl(sum(b.bytes)/1024/1024,0) from dba_free_space b where b.tablespace_name = a.tablespace_name ) as freespace, sum(decode(a.maxbytes,0,a.bytes,a.maxbytes))/1024/1024 as maxsize from dba_data_files a group by a.tablespace_name ) where (allocated-freespace)/maxsize*100 > 95 order by 1; exit
Lastly, create a PowerShell script to send email named as sendmail_tablespace_warn.ps1
# Script to send a mail if a tablespace is > 95% Used. Called by tablespace_check.ps1 # $EmailFrom = "hostname@client.com.au" $EmailTo = "someone@somewhere.com.au" $Subject = "CLIENT:PROD HOST:SID:Tablespace Space:WARNING" $Body = "A Tablespace in SID on the PROD Host is over 80% full" $SMTPServer = "client.smtp.com.au" $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25) $SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password"); $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)