session指令碼監控

oracle@tt> crontab -l

*/10 * * * * sh /exp/sys/session.sh

oracle@ZFKSFCDB01:/exp/sys> ls se*
session  session2  session_2  session_22  session27  session_27  session277  session7  session_7  session_77  session.sh  session.sql

oracle@ZFKSFCDB01:/exp/sys> cat  session.sh
rm /exp/sys/session.sql
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=fksfcdb1
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
/u01/app/oracle/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba << eof
declare n number ;
begin
for h in (select s.PROGRAM,s.TERMINAL,s.SCHEMANAME,s.STATUS,p.spid,s.logon_time
                 from V\$session s ,v\$process p
                      WHERE s.TYPE NOT in ('BACKGROUND') and p.addr like s.paddr)
loop
insert into check_session@check_link(host,PROGRAM,TERMINAL,SCHEMANAME,STATUS,spid,logon_time,RECORD_DATE)
values ('10.182.15.30',h.PROGRAM,h.TERMINAL,h.SCHEMANAME,h.STATUS,h.spid,h.logon_time,sysdate);
end loop ;
select  count(1) into n from v\$session where type not in ('BACKGROUND');
insert into session_all@check_link
(host,session_num,record_date)
values ('10.182.15.30',n,sysdate);
end;
/
commit;
exit;
eof
ps -ef |grep oracle |grep LOCAL > /exp/sys/session
awk '{print $2}' /exp/sys/session > /exp/sys/session2
awk '{print $7}' /exp/sys/session > /exp/sys/session7
cat /exp/sys/session2|sed s/^/,\'\/g>/exp/sys/session_2
cat /exp/sys/session7|sed s/^/,\'\/g>/exp/sys/session_7
cat /exp/sys/session_2|sed s/$/\'\/g>/exp/sys/session_22
cat /exp/sys/session_7|sed s/$/\'\/g>/exp/sys/session_77
cat /exp/sys/session2|sed s/^/insert\ into\ check_temp\ values\(\'10\.182\.15\.30\',\'/g>/exp/sys/session_27
cat /exp/sys/session_27|sed s/$/\'\/g>/exp/sys/session27
paste /exp/sys/session27 /exp/sys/session_77 >/exp/sys/session277
cat /exp/sys/session277|sed s/$/\,sysdate\)\;/g >>/exp/sys/session.sql
echo 'exit' >>/exp/sys/session.sql
export ORACLE_HOME="/u01/app/oracle/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/session.sql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-733984/，如需轉載，請註明出處，否則將追究法律責任。