主要实现功能：
1、监控表空间，通用版
2、采用PHP进行格式化输入输出，并下发监控邮件报表
3、自动维护、优化涉及到的文件及表
4、对特殊格式输入进行demo说明
目前该版本已有升级加强版，对于目前提供的脚本足够大家使用，谢谢指正！
#--SETUP.sql
[Oracle@clement datafile_monitor]$ more SETUP.sql
/*DESCRIBE
VI SETUP.sql
REM INITIALIZATION FILE TO INSTALL THE MONITORING
AUTHOR : Clement Ge
MAIL: Clement.gejun@gmail.com
*/
SET TIMING ON;
SET SERVEROUTPUT ON;
TRUNCATE TABLE DATAFILES_GE_MONITORING;
DROP TABLE DATAFILES_GE_MONITORING;
/* Create table*/
create table DATAFILES_GE_MONITORING
(
TABLESPACE_NAME VARCHAR2(50) not null,
FILE_NAME VARCHAR2(200) not null,
IGBYTES NUMBER(16,6) not null,
CURRENTDAYGBYTES NUMBER(16,6) default 0 not null,
LASTDAYSGBYTES NUMBER(16,6) default 0 not null,
CURRENTWEEKGBYTES NUMBER(16,6) default 0 not null,
LASTWEEKSGBYTES NUMBER(16,6) default 0 not null,
CURRENTMONTHGBYTES NUMBER(16,6) default 0 not null,
LASTMONTHSGBYTES NUMBER(16,6) default 0 not null,
ALIVEVALUE VARCHAR2(40) default (to_char(SYSDATE,'YYYY-MM-DD') || ',,5') not null,
STATUS CHAR(1) default 0 not null
)
/* The current default specified tablespace or whatever*/
--tablespace USERS
pctfree 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
);
/* Create/Recreate primary, unique and foreign key constraints */
ALTER TABLE DATAFILES_GE_MONITORING
ADD CONSTRAINT UNK_DATAFILES_GE_MONITORING PRIMARY KEY (TABLESPACE_NAME, FILE_NAME)
USING INDEX;
/* KEEP TABLE*/
ALTER TABLE DATAFILES_GE_MONITORING STORAGE (BUFFER_POOL KEEP);
/* Initialization value is not less than 1G, and to be calculated in units of G*/
INSERT INTO DATAFILES_GE_MONITORING
SELECT T.TABLESPACE_NAME TABLESPACE_NAME,
T.FILE_NAME FILE_NAME,
T.BYTES / 1024 / 1024 / 1024 IGBYTES,
0 CURRENTDAYGBYTES,
0 LASTDAYSGBYTES,
0 CURRENTWEEKGBYTES,
0 LASTWEEKSGBYTES,
0 CURRENTMONTHGBYTES,
0 LASTMONTHSGBYTES,
(TO_CHAR(SYSDATE,'YYYY-MM-DD') || ',,5') ALIVEVALUE,
(CASE WHEN T.BYTES / 1024 / 1024 / 1024 > 31 THEN 6 ELSE 0 END) STATUS
FROM DBA_DATA_FILES T
WHERE EXISTS (SELECT 1
FROM DBA_DATA_FILES X
WHERE T.TABLESPACE_NAME = X.TABLESPACE_NAME
AND X.BYTES / 1024 / 1024 / 1024 >= 0)
ORDER BY T.TABLESPACE_NAME ASC,T.BYTES DESC;
SHOW ERRORS;
COMMIT;
#--DATAFILES_GE_MONITORING.sh
[oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sh
#!/bin/sh
source /home/oracle/.bash_profile
#select userenv('language') from dual
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
today=`date +%Y_%m_%d -d "$(echo -1) day"`
dirs=`pwd`
cd $dirs
find $dirs -name '*_*_MonitoringDatafileSpace.html' -type f -mtime +7 |xargs rm -rf
sed -e "/html/c\spool "$dirs/$today"_MonitoringDatafileSpace.html" DATAFILES_GE_MONITORING.sql > TEMP_DATAFILES_GE_MONITORING.sql
mv TEMP_DATAFILES_GE_MONITORING.sql DATAFILES_GE_MONITORING.sql
#sqlplus scott/"scott@#$331804"@ucc@DATAFILES_GE_MONITORING.sql
exit
!
#-- sendDataFileSpaceMonitoring.php
[oracle@clement datafile_monitor]$ more sendDataFileSpaceMonitoring.php
#!/usr/bin/php -q
#--DATAFILES_GE_MONITORING.sql
[oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sh
#!/bin/sh
source /home/oracle/.bash_profile
#select userenv('language') from dual
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
today=`date +%Y_%m_%d -d "$(echo -1) day"`
dirs=`pwd`
cd $dirs
find $dirs -name '(*_*_)每日数据维护工作一.html' -type f -mtime +7 |xargs rm -rf
sed -e "/txt/c\spool "$dirs/$today"_每日数据维护工作一.txt" DATAFILES_GE_MONITORING.sql > TEMP_DATAFILES_GE_MONITORING.sql
mv TEMP_DATAFILES_GE_MONITORING.sql DATAFILES_GE_MONITORING.sql
#sqlplus scott/"scott@#$331804"@ucc--SET LINESIZE 1000
--SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 999
--SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
@DATAFILES_GE_MONITORING.sql
exit
!
--DATAFILES_GE_MONITORING.sql
[oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sql
/* DESCRIBE
vi DATAFILES_GE_MONITORING.sql
REM Monitoring analyze
REM Deployment CRON use oracle user
AUTHOR : Clement Ge*/
/* Insert into datas */
MERGE INTO DATAFILES_GE_MONITORING S
USING (SELECT /*+ NO_CPU_COSTING */ T.TABLESPACE_NAME TABLESPACE_NAME,
T.FILE_NAME FILE_NAME,
T.BYTES / 1024 / 1024 / 1024 IGBYTES
FROM DBA_DATA_FILES T
WHERE EXISTS (SELECT 1
FROM DBA_DATA_FILES W
WHERE T.TABLESPACE_NAME = W.TABLESPACE_NAME
AND W.BYTES / 1024 / 1024 / 1024 BETWEEN 1 AND 32)
ORDER BY T.TABLESPACE_NAME ASC,T.BYTES DESC) X
ON (S.TABLESPACE_NAME = X.TABLESPACE_NAME
AND S.FILE_NAME = X.FILE_NAME)
WHEN MATCHED THEN
UPDATE SET S.IGBYTES = X.IGBYTES,
S.CURRENTDAYGBYTES = X.IGBYTES - S.IGBYTES,
S.LASTDAYSGBYTES = S.CURRENTDAYGBYTES,
S.CURRENTWEEKGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'00',S.LASTDAYSGBYTES,S.CURRENTWEEKGBYTES + S.LASTDAYSGBYTES),
S.LASTWEEKSGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'00',S.CURRENTWEEKGBYTES,S.LASTWEEKSGBYTES),
S.CURRENTMONTHGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'01',
S.LASTDAYSGBYTES,S.CURRENTMONTHGBYTES + S.CURRENTWEEKGBYTES),
S.LASTMONTHSGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'01',S.CURRENTMONTHGBYTES,S.LASTMONTHSGBYTES),
S.ALIVEVALUE = (CASE WHEN REPLACE(SUBSTR(S.CURRENTDAYGBYTES,1,7),'-','') > 0 THEN
TO_CHAR(SYSDATE,'YYYY-MM-DD') || ',,' || SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2)+1)
ELSE
(CASE WHEN NVL(SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1)+1,
INSTR(S.ALIVEVALUE,',',1,2)-INSTR(S.ALIVEVALUE,',',1)-1),0)
BETWEEN 0 AND SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2)+1) - 1 THEN
SUBSTR(S.ALIVEVALUE,1,INSTR(S.ALIVEVALUE,',',1)) ||
(NVL(SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1)+1,
INSTR(S.ALIVEVALUE,',',1,2)-INSTR(S.ALIVEVALUE,',',1)-1),0) + 1)||
SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2))
ELSE
S.ALIVEVALUE
END)
END)
WHERE S.STATUS < '9'
WHEN NOT MATCHED THEN
INSERT (S.TABLESPACE_NAME,S.FILE_NAME,S.IGBYTES,S.CURRENTDAYGBYTES,S.LASTDAYSGBYTES,S.CURRENTWEEKGBYTES,S.LASTWEEKSGBYTES,
S.CURRENTMONTHGBYTES,S.LASTMONTHSGBYTES,S.ALIVEVALUE,S.STATUS)
VALUES (X.TABLESPACE_NAME,X.FILE_NAME,X.IGBYTES,0,0,0,0,0,0,(SYSDATE || ',,5'),
(CASE WHEN X.IGBYTES / 1024 / 1024 / 1024 > 31 THEN 2 ELSE 0 END));
/* Update Status*/
UPDATE DATAFILES_GE_MONITORING UNM
SET UNM.STATUS = (CASE
WHEN UNM.IGBYTES < 26 AND NOT EXISTS (SELECT '1'
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
(CASE WHEN (NVL(SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1)+1,
INSTR(UNM.ALIVEVALUE,',',1,2)-INSTR(UNM.ALIVEVALUE,',',1)-1),0) >=
SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1)
AND (UNM.STATUS = '0' OR UNM.STATUS = '1') THEN
'1'
ELSE
'0'
END)
WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1 WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) > 6 AND
EXISTS (SELECT '1' FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
(CASE WHEN (SELECT NVL(MIN(NVL(SUBSTR(UNM1.ALIVEVALUE,INSTR(UNM1.ALIVEVALUE,',',1)+1,
INSTR(UNM1.ALIVEVALUE,',',1,2)-INSTR(UNM1.ALIVEVALUE,',',1)-1),0)),0)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME
AND UNM1.STATUS NOT IN ('2','4')) >=
SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1 THEN
'1'
WHEN NVL(SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1)+1,
INSTR(UNM.ALIVEVALUE,',',1,2)-INSTR(UNM.ALIVEVALUE,',',1)-1),0) >=
SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1 THEN
'3'
ELSE
'0'
END)
WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES) FROM
DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 3 AND 6 THEN '5'
WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 0 AND 2 THEN '6'
ELSE UNM.STATUS
END)
WHERE UNM.STATUS < '9';
COMMIT;
SET LINESIZE 2000;
SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 2000;
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
spool /wins/test/datafile_monitor/2011_05_13_每日数据维护工作一.html;
SELECT ALARM.TABLESPACE_NAME "需处理的表空间名称",
DENSE_RANK() OVER(PARTITION BY ALARM.STATUS,ALARM.TABLESPACE_NAME ORDER BY ALARM.STATUS DESC,
NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) ASC,
ALARM.IGBYTES DESC) "处理优先级",
(CASE WHEN ALARM.STATUS = '6' THEN
(CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 0 THEN
(CASE WHEN NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) > 0 THEN
'报警！需立即处理,否则影响数据正常入库,目前已'|| SUBSTR(ALARM.ALIVEVALUE,
INSTR(ALARM.ALIVEVALUE,',',1)+1,
INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增长！'
ELSE
'报警！！请立即处理！！！'
END)
ELSE '报警！！！请立即处理,该空间达到或超过最大文件自动扩展空间界限,将要影响该空间所有数据正常运行！'
END)
--警告提示级别统计
ELSE
(CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 3 THEN
(CASE WHEN NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) > 0 THEN
'警告 ！需按照业务增长量确定是否立刻调整,目前已'|| SUBSTR(ALARM.ALIVEVALUE,
INSTR(ALARM.ALIVEVALUE,',',1)+1,
INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增长！'
ELSE
'警告 ！！可能需要立即调整！'
END)
ELSE '警告！！！该空间达到或超过警告最大文件空间界限,为了不影响数据的正常运行,请立即处理！'
END)
END) "监控描述信息",
ALARM.IGBYTES "数据文件大小(G)",
ALARM.FILE_NAME "数据文件路径"
FROM DATAFILES_GE_MONITORING ALARM
WHERE ALARM.STATUS IN ('5','6');
UPDATE DATAFILES_GE_MONITORING UNM
SET UNM.STATUS = (CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) < 0 THEN
'4'
ELSE
UNM.STATUS
END)
WHERE UNM.STATUS IN ('5','6');
COMMIT;
WITH ESTIMATE_01 AS (SELECT DECODE(TX.STATUS,'0','监控','1','数据未增长','3','暂时空闲','4','暂停监控','5','警告','6','报警') DS,
TX.TABLESPACE_NAME TB,
TX.IGBYTES IG,
--SUM(TX.IGBYTES) OVER (ORDER BY TX.FILE_NAME) "表空间累计监控值(G)",
TX.CURRENTDAYGBYTES CD,
TX.LASTDAYSGBYTES LD,
(CASE WHEN TX.LASTDAYSGBYTES = 0 THEN
(CASE WHEN TX.CURRENTDAYGBYTES >= 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTDAYGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTDAYGBYTES*100,4) || '%'
END)
WHEN TX.CURRENTDAYGBYTES = 0 THEN
(CASE WHEN TX.LASTDAYSGBYTES > 0 THEN
'Down Rate : ' || ROUND(TX.LASTDAYSGBYTES*100,4) || '%'
ELSE
'Up Rate : ' || ROUND(TX.LASTDAYSGBYTES*100,4) || '%'
END)
ELSE
(CASE WHEN TX.CURRENTDAYGBYTES > 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTDAYGBYTES/TX.LASTDAYSGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTDAYGBYTES/TX.LASTDAYSGBYTES*100,4) || '%'
END)
END) CLD,
TX.CURRENTWEEKGBYTES CW,
TX.LASTWEEKSGBYTES LW,
(CASE WHEN TX.LASTWEEKSGBYTES = 0 THEN
(CASE WHEN TX.CURRENTWEEKGBYTES >= 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTWEEKGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTWEEKGBYTES*100,4) || '%'
END)
WHEN TX.CURRENTWEEKGBYTES = 0 THEN
(CASE WHEN TX.LASTWEEKSGBYTES > 0 THEN
'Down Rate : ' || ROUND(TX.LASTWEEKSGBYTES*100,4) || '%'
ELSE
'Up Rate : ' || ROUND(TX.LASTWEEKSGBYTES*100,4) || '%'
END)
ELSE
(CASE WHEN TX.CURRENTWEEKGBYTES > 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTWEEKGBYTES/TX.LASTWEEKSGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTWEEKGBYTES/TX.LASTWEEKSGBYTES*100,4) || '%'
END)
END) CLW,
TX.CURRENTMONTHGBYTES CM,
TX.LASTMONTHSGBYTES LM,
(CASE WHEN TX.LASTMONTHSGBYTES = 0 THEN
(CASE WHEN TX.CURRENTMONTHGBYTES >= 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTMONTHGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTMONTHGBYTES*100,4) || '%'
END)
WHEN TX.CURRENTMONTHGBYTES = 0 THEN
(CASE WHEN TX.LASTMONTHSGBYTES > 0 THEN
'Down Rate : ' || ROUND(TX.LASTMONTHSGBYTES*100,4) || '%'
ELSE
'Up Rate : ' || ROUND(TX.LASTMONTHSGBYTES*100,4) || '%'
END)
ELSE
(CASE WHEN TX.CURRENTMONTHGBYTES > 0 THEN
'Up Rate : ' || ROUND(TX.CURRENTMONTHGBYTES/TX.LASTMONTHSGBYTES*100,4) || '%'
ELSE
'Down Rate : ' || ROUND(TX.CURRENTMONTHGBYTES/TX.LASTMONTHSGBYTES*100,4) || '%'
END)
END) CLM
FROM DATAFILES_GE_MONITORING TX
WHERE TX.STATUS < '9'
ORDER BY TX.STATUS DESC,TX.IGBYTES DESC)
select DS "文件状态",TB "表空间名称",IG || '' "当前数据文件大小(G)",
CD || '' "当天数据量(G)",LD || '' "昨天数据量(G)",
CLD "前/后两天比率",
CW || '' "本周累计总值(G)",LW || '' "上周总值(G)",
CLW "前/后两周比率",
CM || '' "当月累计总值(G)",LM || '' "上月总值(G)",
CLM "前/后两月比率"
from ESTIMATE_01
UNION ALL
SELECT '统计汇总 ','监控总计：' || ESTIMATE_02.SUMS,'当前监控总计：' || SUM(ESTIMATE_01.IG),
'当天增值总计：' || SUM(ESTIMATE_01.CD),'昨天增值总计：' || SUM(ESTIMATE_01.LD),
'停止监控总计：' || ESTIMATE_02.UNM,
'本周累计总计：' || SUM(ESTIMATE_01.CW),'上周增值总计：' || SUM(ESTIMATE_01.LW),
'空间总计：' || ESTIMATE_03.UNCM,
'本月累计总计：' || SUM(ESTIMATE_01.CM),'上月增值总计：' || SUM(ESTIMATE_01.LM),
'未加入监控总计：' || TRUNC((ESTIMATE_03.UNCM - ESTIMATE_02.SUMS),6)
FROM ESTIMATE_01,
(SELECT SUM(TT.IGBYTES)|| '' SUMS,TRUNC(SUM(DECODE(TT.STATUS,'9',TT.IGBYTES,0)),6)|| '' UNM
FROM DATAFILES_GE_MONITORING TT) ESTIMATE_02,
(SELECT TRUNC(SUM(BYTES)/ 1024 / 1024 / 1024,6) || '' UNCM FROM DBA_DATA_FILES) ESTIMATE_03
GROUP BY ESTIMATE_02.SUMS,ESTIMATE_02.UNM,ESTIMATE_03.UNCM;
SPOOL OFF;
ANALYZE TABLE DATAFILES_GE_MONITORING COMPUTE STATISTICS;