oracle输出html文件,mail发送oracle输出html格式内容

一：随便写一个输出的sql

##Monitoring.sql

--表空间使用情况

SELECT upper(f.tablespace_name) "表空间名",

d.Tot_grootte_Mb||'M' "表空间大小",

d.Tot_grootte_Mb - f.total_bytes||'M' "已使用空间",

to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99')||'%' "使用比",

f.total_bytes||'M' "空闲空间",

f.max_bytes||'M' "最大块"

FROM (SELECT tablespace_name,

round(SUM(bytes)/(1024*1024),2) total_bytes,

round(MAX(bytes)/(1024*1024),2) max_bytes

FROM sys.dba_free_space

GROUP BY tablespace_name) f,

(SELECT dd.tablespace_name,

round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb

FROM sys.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name

and  f.tablespace_name not in('EXAMPLE','USERS','XDB','INDEX')

ORDER BY 4 DESC;

--各用户、表空间使用表表占用空间情况

select distinct owner "oracle用户",tablespace_name "表空间",segment_name "表名称",

round(bytes/(1024*1024),2)||'M'

from Dba_Segments

where bytes/(1024*1024)>10

and segment_name not like '%$'

group by owner,tablespace_name,segment_name,bytes

order by owner,tablespace_name,segment_name,4 desc;

二：通过shell脚本执行上么的sql(已网页格式输出、删除、备份)

#Monitoring.sh

#!/bin/sh

#脚本存放路径

date_time=`date +%Y%m%d`

scriptdir=/data/gejun/oracle_Monitoring

#生成的XLS文件格式：(xxxx_xx_xx.html)

find $scriptdir -name '%_%_%_Monitoring.html' -type f -mtime +2 |xargs rm -rf

cd $scriptdir

sqlplus 'cplatformdata/cplatformdata'<

set linesize 1000

set term off verify off feedback off pagesize 999

set markup html on entmap ON spool on preformat off

spool `date +%Y_%m_%d`_Monitoring.html

@/data/gejun/oracle_Monitoring/oracle_Monitoring.sql

spool off

exit

!

cd $scriptdir

sed '/SQL&gt/'d `date +%Y_%m_%d`_Monitoring.html>Monitoring_temp.html

sed '/Resource/'d Monitoring_temp.html>Monitoring.html

rm -rf Monitoring_temp.html

三：使用php来上面html格式话输出做为mail的内容发送

[root@caiwu oracle_Monitoring]# cat Monitoring.php

#!/usr/bin/php   -q

$email= "mail地址";

$file=fopen("/data/gejun/oracle_Monitoring/Monitoring.html","r");

send_email($email,$file);

function send_email($email,$file){

$header= "Content-type:text/html;charset=gb2312/r/n ";

$header .='From:<

hiho1128@126.com>' ."/r/n ";  ＃这是本人的地址

$from= "

mail地址";

$subject= "11.3 simple Oralce Monitoring";

$message="

";

$message .= "

11.3服务器简单监控信息列表如下
";

$message .=$file;

while (!feof($file))

{

$message .=fgetc($file);

}

fclose($file);

$message .= "
";

mail($email,$subject,$message,$header) or die( "Sorry,Failure ");

}

?>

###版权-----found2008 ------邮箱:hiho1128@126.com