Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Venkata.

Asked: February 03, 2016 - 8:58 pm UTC

Last updated: February 05, 2016 - 1:39 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

How to get the session start and ending time? Based any system historical tables ex:DBA_HIST_ACTIVE_SESS_HISTORY can it be derived? ultimate goal is define sessions_per_user in user profiles based on any existing oracle V$ tables data.

and Connor said...

If you run AUDIT SESSION, then you'll capture logon and logoff time for each session, plus some useful information about how much work they did whilst connected, and you can also get failed login attempts.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Logon and Logoff Timing

Venkata R Yelleswarapu, February 04, 2016 - 9:14 pm UTC

Thank you for providing info quickly, still I am not clear how to derive the logon and logoff for each session into one row, I see the data is inserted into two rows for one session into table dba_audit_session, if already query exist to get the data into one row, can some one point me to that? or provide the query, I appreciate for the help in advance.
Connor McDonald
February 05, 2016 - 1:39 am UTC 

SQL> select sessionid,
  2         username,
  3        min(decode(action_name,'LOGON',TIMESTAMP)) logon_time,
  4        min(decode(action_name,'LOGOFF',TIMESTAMP)) logoff_time
  5  from dba_audit_session
  6  group by sessionid, username
  7  order by 2;

 SESSIONID USERNAME             LOGON_TIME          LOGOFF_TIME
---------- -------------------- ------------------- -------------------
    972164 CUST1                05/02/2016 09:31:40 05/02/2016 09:31:42
    972173 CUST10               05/02/2016 09:31:58 05/02/2016 09:32:00
    972174 CUST11               05/02/2016 09:32:00 05/02/2016 09:32:03
    972175 CUST12               05/02/2016 09:32:03 05/02/2016 09:32:05
    972176 CUST13               05/02/2016 09:32:05 05/02/2016 09:32:07
    972177 CUST14               05/02/2016 09:32:07 05/02/2016 09:32:09
    972178 CUST15               05/02/2016 09:32:09 05/02/2016 09:32:11
    972179 CUST16               05/02/2016 09:32:11 05/02/2016 09:32:13
...
...


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.