主要实现功能：
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,
ALIVeva lUE 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') ALIVeva lUE,
(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< sqlplus 'scott/tiger'< @DATAFILES_GE_MONITORING.sql
exit
!
#-- sendDataFileSpaceMonitoring.php
[oracle@clement datafile_monitor]$ more sendDataFileSpaceMonitoring.php
#!/usr/bin/php -q
<
$email="clement.ge@finalist.hk";
$currenthour=date("H");
$pathnames=dirname(__FILE__)."