Oracle Tablespace Usage Monitor with Email Alerts

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

Oracle tablespaces can grow unexpectedly due to user activity, data load, or long-running transactions. Proactive monitoring ensures that DBAs are notified before tablespaces run out of space.

This shell script automation checks for tablespaces exceeding 90% usage and sends an email alert with detailed tablespace information. It supports both autoextensible and non-autoextensible datafiles.

Full Shell Script with SQL – tablespace_alert.sql

This SQL script checks for any tablespace that has used more than 90% of its allocated space:

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'

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,
         SUM(bytes)/1024/1024 total_tbs_free_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
)
SELECT 
  c.instance_name,
  a.tablespace_name Tablespace,
  NVL(tbs_auto.autoextensible, 'NO') 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,
  tbs_auto
WHERE 
  a.tablespace_name = files.tablespace_name
  AND a.tablespace_name = fragments.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_used_pct DESC;

tablespace_threshold.sh

#!/bin/bash

# ----------------------------------------------
# Tablespace Alert Script
# ----------------------------------------------
# Sends email notification if any tablespace
# crosses 90% threshold
# ----------------------------------------------

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 /u01/app/oracle/scripts

logfile=/u01/app/oracle/scripts/tablespace_alert.log
sqlfile=/u01/app/oracle/scripts/tablespace_alert.sql

# Check if instance is running
if ps -ef | grep [p]mon | grep -q "$ORACLE_SID"; then
  sqlplus -s "/as sysdba" > /dev/null <<EOF
spool $logfile
@$sqlfile
spool off
exit
EOF

  # Send email only if output has more than 4 lines (indicating actual data)
  if [ $(wc -l < "$logfile") -ge 4 ]; then
    mailx -s "⚠️ TABLESPACE ALERT FOR $ORACLE_SID DB" info.w3buddy@gmail.com < "$logfile"
  fi
fi

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Create the SQL file
# ----------------------------------------------
mkdir -p /u01/app/oracle/scripts
vi /u01/app/oracle/scripts/tablespace_alert.sql

# (Paste the SQL script above into the file and save)

# ----------------------------------------------
# 2. Create the shell script
# ----------------------------------------------
vi /u01/app/oracle/scripts/tablespace_threshold.sh

# (Paste the shell script above into the file and save)

# ----------------------------------------------
# 3. Make the script executable
# ----------------------------------------------
chmod +x /u01/app/oracle/scripts/tablespace_threshold.sh

# ----------------------------------------------
# 4. Test the script manually
# ----------------------------------------------
/u01/app/oracle/scripts/tablespace_threshold.sh

# Check the log file for details:
cat /u01/app/oracle/scripts/tablespace_alert.log

# ----------------------------------------------
# 5. Schedule with cron (every 15 mins)
# ----------------------------------------------
crontab -e

# Add this line:
0,15,30,45 * * * * /u01/app/oracle/scripts/tablespace_threshold.sh >> /tmp/ts_monitor.log 2>&1

# ----------------------------------------------
# 6. Confirm the cron entry
# ----------------------------------------------
crontab -l

✅ Notes & Recommendations

• You can adjust the 90% threshold in the SQL query if needed.
• Ensure the email configuration (mailx) is functional.
• This script is compatible with both RAC and single-instance environments.