{"id":4292,"date":"2025-06-17T21:14:52","date_gmt":"2025-06-17T21:14:52","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4292"},"modified":"2025-06-17T21:14:54","modified_gmt":"2025-06-17T21:14:54","slug":"oracle-tablespace-usage-monitor-with-email-alerts","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/shell-scripts\/oracle-tablespace-usage-monitor-with-email-alerts\/","title":{"rendered":"Oracle Tablespace Usage Monitor with Email Alerts"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Full Shell Script with SQL &#8211; tablespace_alert.sql<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This SQL script checks for any tablespace that has used more than <strong>90%<\/strong> of its allocated space:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>set feedback off\nset pagesize 70\nset linesize 2000\nset head on\n\nCOLUMN Tablespace format a25 heading 'Tablespace Name'\nCOLUMN autoextensible format a11 heading 'AutoExtend'\nCOLUMN files_in_tablespace format 999 heading 'Files'\nCOLUMN total_tablespace_space format 99999999 heading 'TotalSpace'\nCOLUMN total_used_space format 99999999 heading 'UsedSpace'\nCOLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'\nCOLUMN total_used_pct format 9999 heading '%Used'\nCOLUMN total_free_pct format 9999 heading '%Free'\n\nWITH tbs_auto AS (\n  SELECT DISTINCT tablespace_name, autoextensible\n  FROM dba_data_files\n  WHERE autoextensible = 'YES'\n),\nfiles AS (\n  SELECT tablespace_name, COUNT(*) tbs_files,\n         SUM(bytes)\/1024\/1024 total_tbs_bytes\n  FROM dba_data_files\n  GROUP BY tablespace_name\n),\nfragments AS (\n  SELECT tablespace_name,\n         SUM(bytes)\/1024\/1024 total_tbs_free_bytes\n  FROM dba_free_space\n  GROUP BY tablespace_name\n)\nSELECT \n  c.instance_name,\n  a.tablespace_name Tablespace,\n  NVL(tbs_auto.autoextensible, 'NO') autoextensible,\n  files.tbs_files files_in_tablespace,\n  files.total_tbs_bytes total_tablespace_space,\n  (files.total_tbs_bytes - fragments.total_tbs_free_bytes) total_used_space,\n  fragments.total_tbs_free_bytes total_tablespace_free_space,\n  ROUND(((files.total_tbs_bytes - fragments.total_tbs_free_bytes)\/ files.total_tbs_bytes) * 100) total_used_pct,\n  ROUND((fragments.total_tbs_free_bytes \/ files.total_tbs_bytes) * 100) total_free_pct\nFROM \n  dba_tablespaces a,\n  v$instance c,\n  files,\n  fragments,\n  tbs_auto\nWHERE \n  a.tablespace_name = files.tablespace_name\n  AND a.tablespace_name = fragments.tablespace_name\n  AND a.tablespace_name = tbs_auto.tablespace_name(+)\n  AND ((files.total_tbs_bytes - fragments.total_tbs_free_bytes)\/ files.total_tbs_bytes)* 100 > 90\nORDER BY total_used_pct DESC;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">tablespace_threshold.sh<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n\n# ----------------------------------------------\n# Tablespace Alert Script\n# ----------------------------------------------\n# Sends email notification if any tablespace\n# crosses 90% threshold\n# ----------------------------------------------\n\nexport ORACLE_HOME=\/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\nexport PATH=$ORACLE_HOME\/bin:$PATH\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/lib\nexport ORACLE_SID=PRODDB\ncd \/u01\/app\/oracle\/scripts\n\nlogfile=\/u01\/app\/oracle\/scripts\/tablespace_alert.log\nsqlfile=\/u01\/app\/oracle\/scripts\/tablespace_alert.sql\n\n# Check if instance is running\nif ps -ef | grep &#91;p]mon | grep -q \"$ORACLE_SID\"; then\n  sqlplus -s \"\/as sysdba\" > \/dev\/null &lt;&lt;EOF\nspool $logfile\n@$sqlfile\nspool off\nexit\nEOF\n\n  # Send email only if output has more than 4 lines (indicating actual data)\n  if &#91; $(wc -l &lt; \"$logfile\") -ge 4 ]; then\n    mailx -s \"\u26a0\ufe0f TABLESPACE ALERT FOR $ORACLE_SID DB\" info.w3buddy@gmail.com &lt; \"$logfile\"\n  fi\nfi<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u2699\ufe0f Setup Instructions<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code># ----------------------------------------------\n# 1. Create the SQL file\n# ----------------------------------------------\nmkdir -p \/u01\/app\/oracle\/scripts\nvi \/u01\/app\/oracle\/scripts\/tablespace_alert.sql\n\n# (Paste the SQL script above into the file and save)\n\n# ----------------------------------------------\n# 2. Create the shell script\n# ----------------------------------------------\nvi \/u01\/app\/oracle\/scripts\/tablespace_threshold.sh\n\n# (Paste the shell script above into the file and save)\n\n# ----------------------------------------------\n# 3. Make the script executable\n# ----------------------------------------------\nchmod +x \/u01\/app\/oracle\/scripts\/tablespace_threshold.sh\n\n# ----------------------------------------------\n# 4. Test the script manually\n# ----------------------------------------------\n\/u01\/app\/oracle\/scripts\/tablespace_threshold.sh\n\n# Check the log file for details:\ncat \/u01\/app\/oracle\/scripts\/tablespace_alert.log\n\n# ----------------------------------------------\n# 5. Schedule with cron (every 15 mins)\n# ----------------------------------------------\ncrontab -e\n\n# Add this line:\n0,15,30,45 * * * * \/u01\/app\/oracle\/scripts\/tablespace_threshold.sh >> \/tmp\/ts_monitor.log 2>&amp;1\n\n# ----------------------------------------------\n# 6. Confirm the cron entry\n# ----------------------------------------------\ncrontab -l<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 Notes &amp; Recommendations<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You can adjust the <strong>90% threshold<\/strong> in the SQL query if needed.<\/li>\n\n\n\n<li>Ensure the email configuration (<code>mailx<\/code>) is functional.<\/li>\n\n\n\n<li>This script is compatible with both <strong>RAC<\/strong> and <strong>single-instance<\/strong> environments.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,990],"class_list":["post-4292","cposts","type-cposts","status-publish","hentry","category-notes","category-shell-scripts"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}