Oracle Database

--
-- List Session Details for a Given Time Period
--
-- s_time format = '16-Jul-2011 06:00:00' 
-- e_time format = '16-Jul-2011 06:00:00' 
-- 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
   to_char(sample_time,'DD Mon HH24:MI') as 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  to_char(sample_time,'DD-Mon-YYYY HH24:MI:SS') 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
/