How Long a Session Has been Idle or Inactive In Oracle Database ?

SQL to find how long a session has been ACTIVE or IDLE, very handy.

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_ETNUMBERIf 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.