The following explains how exactly the Middle tier handles the connection pool and connections to the DB (Oracle 10g):

Inactive session in Oracle by JDBC

What I would like to know from DB point of view is at which point in time a particular session from the connection pool has become active and how long is that particular session or session ID is been active and idle?

The following gets me the no.of sessions made to the DB

( select sysdate, 
         COUNT(*) NO_OF_SESSIONS, 
  from v$session 
  where username IS NOT NULL 
  GROUP BY TRUNC(logon_time,''HH''), 

Unless the application or middle tier supports distinguishing the application sessions from the database sessions (for example setting module, action), this will not happen.

The closest you can get to this is querying V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY, but this is just sampled data, and it requires Enterprise Edition + Diagnostic Pack licensed.

  • Yes there is something (Module or Program: JDBC Thin Client) and each session creates it's unique session ID. Does V$ACTIVE_SESSION_HISTORY tells when was the query fired and how long it was active? Does it requires Enterprise Edition + Diagnostic Pack licensed?
    – ravi2i
    Mar 12 '17 at 10:14

thanks for the tips and suggestions. I managed to get what I want and hope the following helps some one else too.

,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
-- idle time
-- days separately
,substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
-- hours
substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time, prev_sql_id, sql_text
FROM v$session s, v$process p, v$sql v
WHERE s.username IS NOT NULL
-- use outer join to show sniped sessions in
-- v$session that don't have an OS process
AND p.addr = s.paddr (+)
AND s.program LIKE 'JDBC%'
AND s.prev_sql_id=v.sql_id(+)
ORDER BY idle_time;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.