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]

SHELL script SHELL SCRIPT


Related Topics

shell script for file system alert
Shell script for monitoring Alert log
Alert log rotation script in oracle
Shell script to monitor lag in standby datbase using dgmgrl
Shell script to monitor asm diskgroup usage
Tablespace monitoring shell script
Shell script to delete old archives using RMAN
Shell script for monitoring blocking sessions
Shell script to monitor goldengate process
Shell script to report failed login attempt in oracle

You May Also Like

Steps for changing public hostname for a standalone grid infrastructure
SQLCL UTILITY IN ORACLE
How to generate AWR report in RAC
Script to get cpu usage and wait event information in oracle database
ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only
REUSE_DUMPFILES parameter in EXPDP
Apply database patch from OEM Cloud control
oraversion utility in oracle 18c - New feature
How to add a tempfile in primary database in dataguard
Apply database proactive bundle patch in RAC using manual process

From This Website

ORA-32774: more than one file was specified for bigfile tablespace
How to change spfile in Oracle RAC.
How to find cpu and memory information of oracle database server
Useful DGMGRL commands in oracle dataguard
How to flashback a Pluggable database ( PDB) in oracle 12.2
Steps for changing public hostname for a standalone grid infrastructure
How to install trace file analyzer( TFACTL)
Oracle database Security Assessment Tool
Add database as target oracle 12c cloud control
Display sql_id of the sql, using set feedback in oracle 18C - New feature