Sunday, April 28, 2019

List Session Details for a Given Time Period

--
-- List Session Details for a Given Time Period
--
-- s_time format = '04/JAN/2019 04:00:00.000' 
-- e_time format = '04/JAN/2019 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 = '04/JAN/2019 04:00:00.000' 
-- e_time format = '04/JAN/2019 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 /