Weblogic JDBC datasource gives "No resources currently available in pool" with just 1 active connection

Question

I am using weblogic JDBC datasource and my DB is Oracle 10g, below is the configuration.

It used to work fine but suddenly it started giving problem, please see below exception.

Weblogic JDBC datasource,java.sql.SQLException: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool

<?xml version="1.0" encoding="UTF-8"?> <jdbc-data-source

xmlns="http://www.bea.com/ns/weblogic/90" xmlns:sec="http://www.bea.com/ns/weblogic/90/security" xmlns:wls="http://www.bea.com/ns/weblogic/90/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bea.com/ns/weblogic/920 http://www.bea.com/ns/weblogic/920.xsd"> XL-Reference-DS
jdbc:oracle:oci:@abc.COM oracle.jdbc.driver.OracleDriver user DEV_260908 password password dll ocijdbc10 protocol oci oracle.jdbc.V8Compatible true baseDriverClass oracle.jdbc.driver.OracleDriver
1 100 1 true SQL SELECT 1 FROM DUAL

DataJndi OnePhaseCommit

This exception is coming on dev environment where connected user is only one.

I know this is related to pool max size but I also suspect this could be due to Oracle, might be Oracle isn't able to create connections.

My questions:

1. Is there any debug/logging parameter to enable datasource logging so that I can check the number of connections acquired, released, and unused in logs?
2. How can I check oracle connection limit for a particular user?

Answer 1

It sounds like something is gradually leaking connections, as you seem to suspect, and you're eventually reaching the JDBC max pool size. I don't think you're hitting the Oracle limits first as you'd get an ORA exception, and also wouldn't be able to connect to the database directly.

You can configure the data source to collect profile information from the WebLogic console; from the domain structure menu, go to services->JDBC->data sources, pick your connection, and go to the diagnostics tab under configuration. But you might be able to get a rough idea from the basic stats on the pool; go to environment->servers, pick your server, and look at the JDBC tab under monitoring. That shows the active connections and high water mark. If either is the same as your max poole size then that's a pretty good indication you're leaking.

From the Oracle side you can look at the number of sessions allowed with show parameters sessions, but also need to check show parameters processes which may limit it further (and some of these are needed by internals processes like pmon). You can see how many open connections you have with something like select count(*) from v$session where username = 'DEV_260908';. Based on the error I wouldn't expect that to be very close to the session/process limits, but ought to match the active connection count from the console.