Oracle Database Tablespace Monitoring

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)