Last updated on November 28th, 2015 at 12:34 pm
Oracle OC4J connect to database using JDBC connection. Here is the list of some good scripts on JDBC connection
A) To find total number of open database connections for a given JVM PID
SELECT s.process, Count(*) all_count FROM v$session s WHERE s.process IN () GROUP BY s.process
B) To find number of database connections per JVM that were inactive for longer then 30 minutes
SELECT s.process, Count(*) olderConnection_count FROM v$session s WHERE s.process IN ()
and s.last_call_et>=(30*60) and s.status=’INACTIVE’ GROUP BY s.process
C) To find the modules responsible to JDBC connections for a process id
SELECT Count(*), process,machine, program, MODULE FROM v$session s
WHERE s.process IN (‘&id’)GROUP BY process,machine, program, MODULE ORDER BY process,machine, program, MODULE;
column module heading “Module Name” format a48;
column machine heading “Machine Name” format a15;
column process heading “Process ID” format a10;
column inst_id heading “Instance ID” format 99;
prompt
prompt Connection Usage Per Module and process
select to_char(sysdate, ‘dd-mon-yyyy hh24:mi’) Time from dual
/
prompt ~~~~
select count(*), machine, process, module from v$session
where program like ‘JDBC%’ group by machine, process, module order by 1 asc
/