Below script can be configured in crontab to send a notification to the support DBAs in case tablespace usage crosses a threshold.

1. First, make the below .sql file, which will be used inside the shell script.

In this script we have defined the threshold as 90%. You can change it as per your requirement.

cat /export/home/oracle/Housekeeping/scripts/tablespace_alert.sql



set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace        format a25 heading 'Tablespace Name'
COLUMN autoextensible         format a11              heading 'AutoExtend'
COLUMN files_in_tablespace    format 999             heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space       format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct              format 9999      heading '%Used'
COLUMN total_free_pct              format 9999     heading '%Free'
COLUMN max_size_of_tablespace      format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct         format 999.99      heading 'Max%Used'
COLUMN total_auto_free_pct         format 999.99      heading 'Max%Free'
WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES/1024/1024) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES)/1024/1024 total_tbs_free_bytes,
               MAX (BYTES)/1024/1024 max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       ) total_used_space,
       fragments.total_tbs_free_bytes total_tablespace_free_space,
       round((  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       )) total_used_pct,
       round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       )) total_free_pct
  FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+)
and (((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/ files.total_tbs_bytes))* 100 > 90
order by total_free_pct;

2. Now prepare the shell script:

At the beginning of the script, we need to define the env variables like ORACLE_HOME, PATCH, LD_LIBRARY_PATH, ORACLE_SID.

Below is the final script(tablespace_threshold.ksh)

cat /export/home/oracle/Housekeeping/scripts/tablespace_threshold.ksh



#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=PRODDB
cd /export/home/oracle/Housekeeping/scripts
logfile=/export/home/oracle/Housekeeping/scripts/Tablespace_alert.log
cnt1=`ps -ef|grep pmon|grep $ORACLE_SID|wc -l`
if [ $cnt1 -eq 1 ];
then
sqlplus -s "/as sysdba" > /dev/null << EOF
spool $logfile
@/export/home/oracle/Housekeeping/scripts/tablespace_alert.sql
spool off
exit
EOF
# If there are more then these two lines in the output file, mail it.
count=`cat $logfile|wc -l`
#echo $count
if [ $count  -ge 4 ];
 then
  mailx -s "TABLESPACE ALERT FOR PROD DB  " [email protected] <$logfile
fi
fi

3. Now configure in crontab:

0,15,30,45 * * * * /export/home/oracle/Housekeeping/scripts/tablespace_threshold.ksh > /export/home/oracle/Housekeeping/logs/ts_alert.log  2>&1

If this article is helpful to you, please provide comment or write to us [email protected].