Script to monitor jdbc connections

set pause off
REM JDBC Scripts used to clear JDBC INACTIVE connections:

REM Script1: It will capture the complete JDBC Connection repot:

spool prod_jdbc_update_jan12_11.log
select count(*),
substr(machine, 6, instr(machine,'.')-6) machine, process, module from gv$session where program like 'JDBC%'
group by machine, process, module
order by 1 asc ;

prompt 'executing Script2: Capture the all INACTIVE sessions list:...'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10

select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script3: It will capture the JDBC Connections list older than 12 hours:..'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following is the JDBC Connections list older than 12 hours , are killed' from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
and s.last_call_et/3600 > 12
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script 4 : It will capture the list of JDBC Connection list and spool to /tmp/db_prod_kill_jdbc_sessions.lst which has the list to clear sid,serial#...'

set head off
set feedback off
spool /tmp/db_prod_kill_jdbc_sessions.lst

select 'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM gV$SESSION s , gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';

spool off

prompt 'executing Script5: It will capture the list of OS processes corresponding to JDBC Connection list (script 4) and spool to /tmp/jdbc_inactive_session.sh ..'


spool /tmp/jdbc_inactive_session.sh
SELECT ' kill -7 '||s.process
FROM gV$SESSION s ,gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.username is not null
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';
spool off

exit