History of running sessions between 2 dates (dd/mm/yyyy hh24:mi)

set linesize 250
set verify off
set pagesize 9999

COLUMN Time      		FORMAT a20            HEADING 'Sample time'
COLUMN Username      	FORMAT a12            HEADING 'User name'
COLUMN Session_id   	FORMAT 99999          HEADING 'Sess ID'
COLUMN sql_id   		FORMAT a14            HEADING 'SQL ID'
COLUMN program   		FORMAT a33            HEADING 'Program'
COLUMN module   		FORMAT a20            HEADING 'Module'
COLUMN event   			FORMAT a25            HEADING 'Event name'
COLUMN time_waited  	FORMAT 999,999,999    HEADING 'Time waited'
COLUMN owner1			FORMAT a15            HEADING 'Owner 1'
COLUMN object_name		FORMAT a15            HEADING 'Object name 1'
COLUMN owner1			FORMAT a15            HEADING 'Owner 2'
COLUMN p2				FORMAT a15            HEADING 'Object name 2'

SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time, u.username, h.blocking_session block_sid, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name, o1.owner, o1.object_name p2
FROM  DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_OBJECTS o, dba_users u, dba_objects o1
where sample_time between to_date('&begin_date', 'dd/mm/yyyy hh24:mi') and to_date('&end_date', 'dd/mm/yyyy hh24:mi')
and session_type != 'BACKGROUND'
and o.object_id (+) = h.CURRENT_OBJ#
and h.p2 = o1.object_id (+)
and time_waited > 0
and u.user_id = h.user_id
and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time
/

Sessions history for last NNN minutes

set linesize 250
set verify off
set pagesize 9999

COLUMN Time      		FORMAT a24            HEADING 'Sample time'
COLUMN User_id      	FORMAT 99999          HEADING 'User ID'
COLUMN Session_id   	FORMAT 99999          HEADING 'Sess ID'
COLUMN sql_id   		FORMAT a14            HEADING 'SQL ID'
COLUMN program   		FORMAT a33            HEADING 'Program'
COLUMN module   		FORMAT a33            HEADING 'Module'
COLUMN event   			FORMAT a25            HEADING 'Event name'
COLUMN time_waited  	FORMAT 999,999,999    HEADING 'Time waited'
COLUMN owner			FORMAT a15            HEADING 'Owner'
COLUMN object_name		FORMAT a35            HEADING 'Object name'

SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time, h.user_id, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name
FROM  v$ACTIVE_SESSION_HISTORY h, DBA_OBJECTS o
where sample_time between sysdate-1/(24*60/&MINUTES_BACK) and sysdate
and session_type != 'BACKGROUND'
and o.object_id (+) = h.CURRENT_OBJ#
and time_waited > 0
and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time
/

SQL execution history (ASH)

set linesize 230
set pagesize 9999

select cpu_time_delta,elapsed_time_delta,iowait_delta,plan_hash_value, EXECUTIONS_DELTA, round(elapsed_time_delta/EXECUTIONS_DELTA/1000/1000, 4) avg_exec_time_sec,
	(select to_char(end_interval_time, 'dd/mm/yyyy hh24:mi') from dba_hist_snapshot hs where hs.snap_id = a.snap_id) end_date from dba_hist_sqlstat a , 
	dba_hist_sqltext b where a.sql_id=b.sql_id and 
	snap_id in (select distinct snap_id from dba_hist_snapshot where end_interval_time>=sysdate-&days_back)
	and a.sql_id = '&sql_id'
	and EXECUTIONS_DELTA != 0
	order by snap_id;	

SQL execution history (cache)

set linesize 230
set pagesize 9999

select cpu_time,elapsed_time,plan_hash_value, EXECUTIONS, round(elapsed_time/EXECUTIONS/1000/1000, 4) avg_exec_time_sec
 from v$sqlstats where sql_id = '&SQL_ID';