/".date('Y_m_d',mktime($currenthour-24))."_MonitoringDatafileSpace.html";
$file=fopen("$pathnames","r");
echo $file;
send_email_tj($email,$file);
function send_email_tj($email,$file){
$header= "Content-type:text/html;charset=gb2312\r\n ";
$header .='From:<clement.ge@finalist.hk>' ."\r\n ";
$from= "clement.ge@finalist.hk";
$subject= "日常数据库数据文件监控列表(统计监控截至时间是当前执行时间)";
$message=" <html> ";
$message .= "

日常数据库监控维护列表

";
$message .=$file;
while (!feof($file))
{
$message .=fgetc($file);
}
fclose($file);
$message .= "
";
mail($email,$subject,$message,$header) or die( "Sorry,Failure ");
}
>

#--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< sqlplus 'scott/tiger'< --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.ALIVeva lUE = (CASE WHEN REPLACE(SUBSTR(S.CURRENTDAYGBYTES,1,7),'-','') > 0 THEN
TO_CHAR(SYSDATE,'YYYY-MM-DD') || ',,' || SUBSTR(S.ALIVeva lUE,INSTR(S.ALIVeva lUE,',',1,2)+1)
ELSE
(CASE WHEN NVL(SUBSTR(S.ALIVeva lUE,INSTR(S.ALIVeva lUE,',',1)+1,
INSTR(S.ALIVeva lUE,',',1,2)-INSTR(S.ALIVeva lUE,',',1)-1),0)

Oracle tablespace监控项目版(二)

日常数据库监控维护列表