SYMPTOMS
There are up to one thousand sessions connected to Oracle Database from a Weblogic connection pool. User wanted to know how long those sessions have been idle ( INACTIVE), so the Weblogic connection pool can be reviewed and reconfigured properly.
SOLUTION
The below query can be run to meet this requirement:
SQL>select USERNAME,
MACHINE,
STATUS,
LOGON_TIME,
LAST_CALL_ET INACTIVE_SECONDS
from gv$session
order by LAST_CALL_ET desc;
USERNAME MACHINE STATUS LOGON_TIME INACTIVE_SECONDS
---------- ---------- ------- ----------------- ----------------
USERAPP machine1 INACTIVE 20170917-17:04:17 3523
USERAPP machine2 INACTIVE 20170918-02:57:03 2068
USERAPP machine3 INACTIVE 20170917-17:52:09 1141
...
..
.
USERAPP machine1 INACTIVE 20170917-17:17:01 176
USERAPP machine1 I NACTIVE 20170917-17:17:01 176
REFERENCES
According to Oracle doc :
LAST_CALL_ET | NUMBER | If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive. |
The below query can also get the results as per Oracle Doc ID 365693.1.
SQL>select sid, seconds_in_wait
from v$session_wait
where state='WAITING'
and event='SQL*Net message from client'
order by seconds_in_wait desc;
Make DBA Life Easy 