JDBC connection scripts for Oracle Applications R12

This article is part of EBS tutorials and provides different JDBC connection scripts for Oracle Applications R12

Oracle  OC4J connect to Oracle database using JDBC connection. Here is the list of some good scripts on JDBC connection

The OC4J JVM process id can be found using the below opmnctl command

opmnctl status -l

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 (<JVM PIDs>) 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 (<JVM PIDs>)
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;

D)  Monitor the JDBC connection counts

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
/

E)Use the script to determine “active users” for OACoreGroup

select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < limit_connects;

F) Active connection which are taking more than 20 min to run

select * 
from v$session 
where program like '%JDBC%' 
and last_call_et > 1200 
and status = 'ACTIVE' 
order by last_call_et asc 
/ 

G) Statements from JDBC connections taking more than 10 min to run

select s.process, s.sid,  t.sql_text 
from v$session s, v$sql t 
where s.sql_address =t.address  
and s.sql_hash_value =t.hash_value 
and s.program like '%JDBC%' 
and s.last_call_et > 600 
and s.status = 'ACTIVE' 
/ 

Related Articles

Queries to check locks in oracle database

Query to check table size in Oracle

How to check Patch application in Oracle E-Business Suite instance R12.1/R12.2

How to check if Port pool is free in Oracle applications R12