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:\scriptsRemove-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 offset lines 120 pages 1000col tablespace_name format a20col allocated format 9999990.99col freespace format 9999990.99col maxsize format 9999990.99col pctused format 90.99select 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)
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)