利用139的邮箱，把自已定制的数库警告发到139邮箱并同时发到手机里

cat /home/oracle/send_warning.sh
#!/bin/sh
DB_USER=test #DB USER
DB_PWD=test #DB PASSWORD
DB_SERV=test #DB SERVICE NAME
rm -rf /home/oracle/warning.txt

send_day=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<exec p_get_warning;
set time off
set heading on
set linesize 300
set trimspool on
col session_count 999999
col sys_time a20
col owner format a20
col object_name format a20
col sid format 999999999
col serial# format 999999
col ctime format 999999
col action a70

spool /home/oracle/warning.txt
prompt -------------------------The number of current connection:------------------;
select * from db_warning_session;
prompt -------------------------The information about the locking:-----------------;
select * from db_warning_lock;
spool off
/
!`
a=`cat /home/oracle/warning.txt |wc -l`
if [ $a -gt 8 ]
then
echo "database warning"|mutt -s "database waring" -a /home/oracle/warning.txt 13696xxxxxxx@139.com
fi

说明：$a -gt 8的目的是当/home/oracle/warning.txt大于8行的时候说明数据库已经有警告信息了，这个时候才发邮件。这个值是自己依据空记录时定的，如下说明没有信息

-------------------------The number of current connection:------------------

no rows selected

-------------------------The information about the locking:-----------------

no rows selected

以下为p_get_warning的内容,只监控session数和lock状态。

sys用户把一些字典授权给test

create view v_sys_session as select * from v$session;
create view v_sys_lock as select * from v$lock;
create view v_sys_dba_objects as select * from sys.dba_objects ;
create view v_sys_locked_object as select * from v$locked_object;

grant select on v_sys_session to test;
grant select on v_sys_lock to test;
grant select on v_sys_dba_objects to test;
grant select on v_sys_locked_object to test;

-- Create table
create table DB_WARNING_LOCK
(
OWNER VARCHAR2(50),
OBJECT_NAME VARCHAR2(50),
SID NUMBER,
SERIAL# NUMBER,
CTIME NUMBER,
ACTION VARCHAR2(50)
)
;
comment on table DB_WARNING_LOCK
is '当前数据库锁定对象表';
-- Add comments to the columns
comment on column DB_WARNING_LOCK.OWNER
is '对象归属';
comment on column DB_WARNING_LOCK.OBJECT_NAME
is '对象名';
comment on column DB_WARNING_LOCK.SID
is 'SID';
comment on column DB_WARNING_LOCK.SERIAL#
is '序列';
comment on column DB_WARNING_LOCK.CTIME
is '锁定时长单位 秒';
comment on column DB_WARNING_LOCK.ACTION
is '解决方法';

-- Create table
create table DB_WARNING_SESSION
(
SESSION_COUNT NUMBER,
SYS_TIME DATE
);
-- Add comments to the table
comment on table DB_WARNING_SESSION
is '当前数据库连数';
-- Add comments to the columns
comment on column DB_WARNING_SESSION.SESSION_COUNT
is '当前连接数';
comment on column DB_WARNING_SESSION.SYS_TIME
is '当时时间';
*/

create or replace procedure p_get_warning is
v_cn number;
begin


delete from db_warning_session;
delete from db_warning_lock;

execute immediate 'select count(*) from v$session'
into v_cn;
if v_cn > 500 then
insert into db_warning_session values (v_cn, sysdate);
end if;

insert into db_warning_lock
select b.owner, b.object_name, c.sid SID, e.serial#, c.ctime, null
from sys.v_sys_locked_object a,
sys.v_sys_dba_objects b,
sys.v_sys_lock c,
dba_blockers d,
sys.v_sys_session e
where a.object_id = b.object_id
and a.session_id = c.sid
and c.sid = d.holding_session
and d.holding_session = e.sid
and c.type = 'TX'
order by ctime desc;
update db_warning_lock a
set a.action = 'alter system kill session ' || '''' || a.sid || ',' || a.serial# || '''';
commit;
end p_get_warning;

[@more@]

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

转载于:http://blog.itpub.net/67798/viewspace-1030759/