List Session Details for a Given Time Period

--
-- List Session Details for a Given Time Period
--
-- s_time format = '22/OCT/2014 04:00:00.000' 
-- e_time format = '23/OCT/2014 04:00:00.000'  
-- inst_no = Instance Number for RAC.  Use 1 for non RAC
--
 
SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET HEADING ON
SET LINESIZE 300
SET PAGESIZE 60
 
COLUMN Sample_Time FOR A12
COLUMN username FOR A20
COLUMN sql_text FOR A40
COLUMN program FOR A40
COLUMN module FOR A40
 
SELECT
   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
BETWEEN '&s_time' and '&e_time'
AND
   INSTANCE_NUMBER=&inst_no
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY 1
/


--
-- List All Columns in dba_hist_active_sess_history for a Given Time Period
--
-- Best run from a GUI like SQL Developer, Toad etc.
--
-- s_time format = '22/OCT/2014 04:00:00.000' 
-- e_time format = '23/OCT/2014 04:00:00.000' 
--
 
SELECT * 
FROM dba_hist_active_sess_history 
WHERE sample_time 
BETWEEN '&s_time' 
AND '&e_time' 
ORDER BY sample_time ASC
/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License