Jboss Datasource Oracle configuration

Question

I all,

I'm using jboss 5.1.0 jdk 1.6. When my WebAPP don't have users working, i execute the next sql to see sessions on my Oracle DB:

 SELECT A.INST_ID,A.USERNAME,A.STATUS,A.SCHEMANAME,A.MACHINE,A.PROGRAM, 
      TO_CHAR((SYSDATE -((1/86400)*LAST_CALL_ET)),'YYYY-MM-DD HH24:MI:SS') LAST_CALL, 
      TO_CHAR(A.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME, 
      A.SID,A.SERIAL#, 
A.FAILOVER_TYPE,A.FAILED_OVER, 
A.OSUSER,A.TERMINAL,A.TYPE,A.MODULE,A.SERVER,A.LOCKWAIT,A.RESOURCE_CONSUMER_GROUP 
FROM GV$SESSION A  
WHERE A.SCHEMANAME IN ('USER_DB') 
ORDER BY USERNAME,MACHINE,LAST_CALL ASC

and the result is:

1   USR_DB  INACTIVE    USR_DB  machine_name  JDBC Thin Client  2012-02-20 11:05:16 2012-02-20 11:04:46 24  5001    NONE    NO  root    unknown USER    JDBC Thin Client    DEDICATED       OTHER_GROUPS

2   USR_DB  ACTIVE      USR_DB  machine2_name  SQL Developer    2012-02-21 09:05:13 2012-02-21 09:03:23 31  3997    NONE    NO  clien2  unknown USER    SQL Developer   DEDICATED       OTHER_GROUPS

The first result, is an inactive session of my WebAPP, the second, is my active session of SQL Developer. My question is: How can i remove or clean from the view GV$SESSION inactive sessions?. Here is my oracle-ds.xml:

   <?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
        <jndi-name>/MyDS</jndi-name>
        <connection-url>jdbc:oracle:thin:@//myhost:1521/xe</connection-url>
        <driver-class>oracle.jdbc.OracleDriver</driver-class>
        <user-name>USR_DB</user-name>
        <password>password</password>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>5</idle-timeout-minutes>
        <check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql>
        <background-validation-millis>400000</background-validation-millis>
        <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
        <metadata>
          <type-mapping>Oracle9i</type-mapping>
        </metadata>
</local-tx-datasource>
</datasources>

There are an special config parameter to force inactive connecionts don't appear or remove it in view GV$SESSION? In my app, i'm sure that i return connections to the pool.

Thanks in advance.

Justin Cave Justin Cave · Accepted Answer · 2012-02-21T08:31:30

Most of the time, your sessions will and should be INACTIVE.

An INACTIVE session merely means that at that particular instant, that session is not executing any particular SQL statement. It doesn't imply that the session needs to get cleaned up. And it has nothing to do with whether your application is returning connections to the pool. It simply means that there is an open database connection that is not at this instant executing a SQL statement.

You could, of course, query GV$session looking for only ACTIVE sessions by adding a WHERE status = 'ACTIVE' to the query. But that will probably exclude most of the sessions opened by the middle tier since most of the time they're not going to be executing SQL statements.

Jboss Datasource Oracle configuration

1 Answers