Windows系统下Oracle表空间满量邮件告警及定时任务配置问询

1. 预处理SQL脚本 #

首先把你提供的SQL优化后保存为check_ts_usage.sql，放在固定路径比如D:\oracle_alert\下，优化点是增加临时表清理逻辑，避免重复执行报错：

SET ECHO OFF; SET TERM OFF; 
SET TIMING OFF; 
SET HEAD OFF; 
SET FEED OFF;
-- 先清理可能存在的临时表
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE temp_ts PURGE';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN -- 忽略表不存在的报错
         RAISE;
      END IF;
END;
/
CREATE TABLE temp_ts(tablespace_name,total_bytes,free_bytes,max_chunk)   AS   SELECT
   tablespace_name, NVL(SUM(bytes), 1), 1, 1   FROM dba_data_files  
   GROUP BY tablespace_name;   
UPDATE temp_ts a   SET a.free_bytes =
   (SELECT NVL(SUM(b.bytes), 1)
                         FROM dba_free_space b
                         WHERE b.tablespace_name = a.tablespace_name);   
COMMIT;
UPDATE temp_ts a   SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)
                        FROM dba_free_space b
                        WHERE b.tablespace_name = a.tablespace_name);   
COMMIT;   
REM ************************   
REM 查询使用率超过95%的表空间
SELECT tablespace_name                        
   ||
            ' is '                                              ||
            TO_CHAR(ROUND(100-(free_bytes*100/total_bytes), 2)) ||
            '% full.' Tablename   FROM temp_ts   WHERE 95 < 100-(free_bytes*100/total_bytes)   ORDER BY tablespace_name;
-- 执行完清理临时表
DROP TABLE temp_ts PURGE;
EXIT;

2. 编写可调度的批处理脚本 #

新建批处理文件ts_alert.bat，同样放在D:\oracle_alert\路径下，内容如下，注意替换占位符为你自己的实际配置：

@echo off
setlocal
:: 配置Oracle环境变量，替换为实际的ORACLE_HOME路径
set ORACLE_HOME=C:\app\oracle\product\19.0.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
:: 配置数据库连接信息，替换为实际的用户名、密码、实例名
set DB_USER=sys
set DB_PWD=your_sys_password
set DB_INSTANCE=orcl
:: 配置告警相关路径和邮件参数
set ALERT_PATH=D:\oracle_alert
set OUTPUT_FILE=%ALERT_PATH%\ts_alert_result.txt
set SMTP_SERVER=smtp.xxx.com
set SMTP_PORT=587
set SMTP_USER=alert@xxx.com
set SMTP_PWD=your_email_password
set RECV_MAIL=admin@xxx.com

:: 清空上次的输出文件
if exist %OUTPUT_FILE% del %OUTPUT_FILE%

:: 执行SQL脚本查询使用率超标的表空间
sqlplus -s %DB_USER%/%DB_PWD%@%DB_INSTANCE% as sysdba @%ALERT_PATH%\check_ts_usage.sql > %OUTPUT_FILE%

:: 判断输出文件是否有内容，有则发送告警邮件
for %%f in (%OUTPUT_FILE%) do if %%~zf gtr 0 (
    powershell -Command "Send-MailMessage -From '%SMTP_USER%' -To '%RECV_MAIL%' -Subject 'Oracle表空间使用率告警' -Body (Get-Content '%OUTPUT_FILE%' | Out-String) -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object System.Management.Automation.PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PWD%' -AsPlainText -Force)))"
)

endlocal

注意：如果你的SMTP服务器不需要SSL，把上述PowerShell命令里的-UseSsl参数去掉即可。

3. 配置Windows任务调度器 #

• 打开Windows任务调度器，选择「创建基本任务」，设置任务名称比如「Oracle表空间告警检查」
• 触发器选择按周期执行，建议每30分钟或者1小时执行一次，根据业务需求调整频率
• 操作选择「启动程序」，程序或脚本选择刚才编写的D:\oracle_alert\ts_alert.bat，起始于填写D:\oracle_alert\
• 安全选项里选择「不管用户是否登录都要运行」，勾选「使用最高权限运行」，避免权限不足导致执行失败
• 保存任务后可以手动右键运行一次，测试是否能正常执行、收到告警邮件

可选优化建议 #

• 可以把批处理里的明文密码替换为加密存储的方式，避免信息泄露
• 如果需要监控临时表空间，在SQL脚本里补充对dba_temp_files的查询逻辑即可
• 可以在告警邮件里增加实例名、服务器IP等信息，方便多实例部署时快速定位问题

内容的提问来源于stack exchange，提问作者ZOHAN