SQL Query to Find Out Oracle Session Details for a Past Time Period from the History tables

advertisements

_____________________________________________________________________________________________________________________

There are the 3 views can be used to achieve this goal. The columns are almost similar to the V$SESSION table except snap_id, sample_id, sample_time etc.. The AWR,

ASH reports are getting populated from these DBA_HIST_ ACTIVE_SESS_ HISTORY views.

Views

a. V$ACTIVE_SESSION_HISTORY 

b. DBA_HIST_ACTIVE_SESS_HISTORY 

c. G V$ACTIVE_SESSION_HISTORY 

I have added 2 queries below and you may add the criteria where clause to get your expected results.

advertisements

 

col program for a35 
col module for a40
col event for a30
set pages 300 lines 220
SELECT SESSION_ID, program, module , event,sql_id ,TIME_WAITED,SESSION_STATE 
FROM  dba_hist_active_sess_history 
WHERE sample_time between to_date('19-06-2020 08:52:00','dd-mm-yyyy hh24:mi:ss') 
and  to_date('19-06-2020 15:37:00','dd-mm-yyyy hh24:mi:ss')   ;
SESSION_ID PROGRAM                           MODULE                                 EVENT                         SQL_ID       TIME_WAITED SESSION
---------- ----------------------------------- ---------------------------------------- ------------------------------ ------------- ----------- -------
       393 oracle@TESTBOX (PSP0)                                                                                                        0 ON CPU
       393 oracle@TESTBOX (PSP0)                                                                                                        0 ON CPU


col program for a35 

col module for a40

col event for a30

set pages 300 lines 220

SELECT SESSION_ID, program, module , event,sql_id ,TIME_WAITED,SESSION_STATE 

FROM  gv$active_session_history

WHERE sample_time between to_date('19-06-2020 08:52:00','dd-mm-yyyy hh24:mi:ss') 

and  to_date('19-06-2020 15:37:00','dd-mm-yyyy hh24:mi:ss'; 
 
SESSION_ID PROGRAM                                  MODULE                                                       EVENT                                    SQL_ID                 TIME_WAITED SESSION
---------- ----------------------------------- ---------------------------------------- ------------------------------ ------------- ----------- -------
       415 oracle@TESTBOX (LREG)                                                                                                                                                  0 ON CPU
       393 oracle@TESTBOX (PSP0)                                                                                                                                                  0 ON CPU
       406 oracle@TESTBOX (DIA0)                                                                                                                                                  0 ON CPU
       415 oracle@TESTBOX (LREG)                                                                                                                                                  0 ON CPU
       390 sqlplus@TESTBOX (TNS V1-V3)             sqlplus@TESTBOX (TNS V1-V3)                                                                0ptw29ufyp3cq         0 ON CPU
       393 oracle@TESTBOX (PSP0)                                                                                                                                                  0 ON CPU
       409 oracle@TESTBOX (CKPT)                                                                      control file parallel write                                 2470 WAITING


       409 oracle@TESTBOX (CKPT)                                                                      control file parallel write                                71500 WAITING

_____________________________________________________________________________________________________________________

Website Stats