定时执行ORACLE脚本，并发邮件到指定邮箱

1.远程连接数据库，并登录执行相应查询语句

修改$ORACLE_HOME/network/admin/tnsnam.ora

119 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

书写远程连接和执行脚本

#!/bin/sh
su - oracle -c "sqlplus 'user/passwd@119 " <@/data/admin/job

@/data/admin/space
quit
EOF
2.书写查询语句/data/admin/job.sql

set pages 50000;
set lines 200;
set trims on;
spool /data/admin/job;
select job,to_char(last_date,'YYYY-MM-DD HH24:MI:SS') last_date,to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date,broken broken ,failures from user_jobs ;

col "Tablespace Name" for a20
col "Total Size" for a10
col "Used MB" for a15
col "Free MB" for a15
spool /data/admin/job append (附加到job.lst中查看表空间利用情况）
select b.file_id "File#",b.tablespace_name "Tablespace Name",b.bytes/1048576||'M' "Total Size",(b.bytes-sum(nvl(a.bytes,0)))/1048576||'M' "Used MB",sum(nvl(a.bytes/1048576,0))||'M' "Free MB",sum(nvl(a.bytes,0))/(b.bytes)*100 "Free%" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;

spool off;
3.生成可写的SPOOL文件 job.lst 并chmod 755权限，可以让ORACLE写入查询结果。

4.用MAIL命令行EMAIL到指定的多个用户，中间用空格就行。

cat /data/admin/job.lst |mail -s "oracle synlog " user@email.com user2@email.com

5.crontab -e 定时执行查询语句和发邮件到指定帐号

00 7 * * * /data/admin/synjob.sh
30 7 * * * /data/admin/mail.sh

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/57915/viewspace-842432/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/57915/viewspace-842432/