• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Ebuisness Suite » JDBC connection scripts for Oracle Applications R12

JDBC connection scripts for Oracle Applications R12

May 29, 2019 by techgoeasy Leave a Comment

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

Filed Under: Oracle, Oracle Ebuisness Suite Tagged With: JDBC connection

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us