Troubleshooting JDBC Connection Leaks in Oracle E-Business Suite 12.2 – Complete Guide with All Queries

Troubleshooting JDBC Connection Leaks in Oracle E-Business Suite 12.2 – Complete Guide

Posted by Apps DBA Stuff | May 2026

Connection leaks in JDBC pools are one of the most common and critical performance issues in Oracle E-Business Suite 12.2 environments. They silently consume database sessions, leading to high CPU usage, ORA-00020: maximum number of processes exceeded, and “Unable to get connection from datasource” errors.

The classic symptom is a large number of INACTIVE sessions in the database with the wait event SQL*Net message from client and long idle time (LAST_CALL_ET > 3600 seconds).

This article provides a deep technical guide with all essential queries you need as an EBS DBA.

---

Understanding the Problem

When an EBS application (oacore, forms, or custom Java code) borrows a connection from the WebLogic pool but never returns it (missing close()), the database sees it as an idle session, while WebLogic still considers it as reserved. Over time, the connection pool exhausts and the application becomes unresponsive.

1. Real-Time Diagnosis Queries

Primary Query – Detailed Leaked Sessions

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,
       ROUND(s.last_call_et / 3600, 2) AS idle_hours,
       s.program,
       s.machine,
       s.module,
       s.action,
       s.client_info,
       s.sql_id,
       s.prev_sql_id,
       p.spid AS db_os_pid,
       s.logon_time,
       s.last_call_et AS idle_seconds
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.status = 'INACTIVE'
  AND s.event = 'SQL*Net message from client'
  AND s.last_call_et > 3600
  AND s.username IN ('APPS', 'APPLSYSPUB')
ORDER BY s.last_call_et DESC;

Summary Count by Source

SELECT machine, program, module, action, 
       COUNT(*) AS session_count,
       MAX(last_call_et)/3600 AS max_idle_hours
FROM v$session
WHERE status = 'INACTIVE'
  AND event = 'SQL*Net message from client'
  AND last_call_et > 1800
GROUP BY machine, program, module, action
ORDER BY session_count DESC;

For RAC Environments

SELECT inst_id, sid, serial#, username, status, event,
       ROUND(last_call_et/3600,2) AS idle_hours,
       program, machine, module
FROM gv$session
WHERE status = 'INACTIVE'
  AND event = 'SQL*Net message from client'
  AND last_call_et > 3600
ORDER BY inst_id, last_call_et DESC;

2. SQL Text of Leaked Sessions

SELECT s.sid, s.serial#, s.username, s.program, s.module,
       s.last_call_et/60 AS idle_minutes,
       sq.sql_fulltext AS current_sql,
       prev.sql_fulltext AS previous_sql
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
LEFT JOIN v$sql prev ON s.prev_sql_id = prev.sql_id
WHERE s.status = 'INACTIVE'
  AND s.event = 'SQL*Net message from client'
  AND s.last_call_et > 3600;

3. Historical Analysis (ASH/AWR)

Top Modules & Programs Causing Leaks (Last 7 Days)

SELECT module, program, COUNT(*) AS total_samples,
       ROUND(COUNT(*)*100.0/SUM(COUNT(*)) OVER(), 2) AS pct
FROM dba_hist_active_sess_history
WHERE event = 'SQL*Net message from client'
  AND sample_time > SYSDATE - 7
GROUP BY module, program
ORDER BY total_samples DESC;

4. Full Diagnostic Master Query (Recommended)

SET LINES 200 PAGES 100
COL username FORMAT A10
COL status FORMAT A8
COL event FORMAT A30
COL program FORMAT A40
COL machine FORMAT A25
COL module FORMAT A30
COL action FORMAT A30
COL idle_hours FORMAT 999.99

SELECT s.inst_id, s.sid, s.serial#, s.username, s.status, s.event,
       ROUND(s.last_call_et/3600,2) AS idle_hours,
       s.program, s.machine, s.module, s.action, s.sql_id
FROM gv$session s
WHERE s.status = 'INACTIVE'
  AND s.event = 'SQL*Net message from client'
  AND s.last_call_et > 1800
ORDER BY s.last_call_et DESC;

5. Kill Session Scripts (Use Carefully!)

-- Generate Kill Commands
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE status = 'INACTIVE'
  AND event = 'SQL*Net message from client'
  AND last_call_et > 7200
  AND username = 'APPS';

Resolution Steps (EBS 12.2 Specific)

• Set Inactive Connection Timeout (Most Important)
  Edit context file: s_jdbc_inactive_timeout → 900 (15 minutes recommended)
  Run AutoConfig
• Enable Connection Leak Profiling
  Set s_jdbc_profile_leak_timeout = 300
  Restart oacore managed servers
• Monitor WebLogic logs for BEA-001153 warnings. The stack trace will point to the exact Java class or OAF controller causing the leak.

Common Causes in EBS 12.2

• Custom OAF pages without proper releaseConnection()
• Java Concurrent Programs missing finally block
• Long-running Self-Service pages
• Incorrect usage of AppsContext.getJDBCConnection()

Prevention Tips

• Always use try-with-resources or proper finally blocks
• Regularly review custom code
• Apply latest EBS JDBC and WebLogic patches
• Monitor leaked connection count daily

Conclusion

JDBC connection leaks are preventable if you monitor proactively using the above queries and configure proper timeout settings. Treat Inactive Connection Timeout as a safety net, not a permanent solution — always fix the root cause in the application code.

Have you faced severe connection leaks in your EBS environment?
Share your experience, number of sessions, or specific module in the comments below.

---

Tags: Oracle EBS 12.2, JDBC Connection Leak, WebLogic, Inactive Sessions, SQL*Net message from client, EBS Performance Tuning, Apps DBA, AutoConfig