Get Active Session or logins from Last weeks in Database

  Once a while it’s very difficult to find sessions history on database. one of my required in my daily dba job once while where we need to find who are active users are logged in last few weeks or months. You can achieve this by using different ways. Here is one of the way you can get some details information by using    DBA_HIST_ACTIVE_SESS_HISTORY,  DBA_USERS ,   DBA_HIST_SQLTEXT Views.

You can use below queries to get required details.

 Find All Active Sessions or logged in session in Database in last 30 days:-

SELECT

   h.sample_time,

   u.username,

   h.program,

   h.module,

   s.sql_text

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time >= SYSDATE - 30

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD and u.username not in ('SYS','SYSTEM','DBSNMP')

ORDER BY h.sample_time

/

Another query to get more details :-

select

*

from DBA_HIST_ACTIVE_SESS_HISTORY

where

sample_time

between

to_date('20-OCT-2017 10:40:00','DD-MON-YYYY HH24:MI:SS')

and

to_date('02-NOV-2017 10:40:10','DD-MON-YYYY HH24:MI:SS')

and instance_number in (1,3) and SESSION_TYPE <> 'BACKGROUND'

order by sample_time,instance_number,SESSION_ID;