利用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 <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
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@]