Time stamp of Active and Inactive session by JDBC 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, 
         TRUNC(logon_time,''HH''), 
         COUNT(*) NO_OF_SESSIONS, 
         username 
  from v$session 
  where username IS NOT NULL 
  GROUP BY TRUNC(logon_time,''HH''), 
           USERNAME
)
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.