mail发送oracle输出html格式内容

最新推荐文章于 2024-08-20 21:30:00 发布

Patrick-Leo

最新推荐文章于 2024-08-20 21:30:00 发布

阅读量1.4k

点赞数

分类专栏： UNIX/LINUX PL/SQL 文章标签： oracle html email header file date

本文链接：https://blog.csdn.net/found2008/article/details/3917451

版权

PL/SQL 同时被 2 个专栏收录

45 篇文章 0 订阅

订阅专栏

UNIX/LINUX

28 篇文章 0 订阅

订阅专栏

一：随便写一个输出的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=" <html><body> ";
$message .= " <br> <br><h1><center>11.3服务器简单监控信息列表如下<center></h1> <br> <br> ";
$message .=$file;
while (!feof($file))
{
$message .=fgetc($file);
}
fclose($file);
$message .= "<br></body></html> ";
mail($email,$subject,$message,$header) or die( "Sorry,Failure ");
}
?>

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