oracle-wiki.net - List Session Details for a Given Time Period

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

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

Notes on List Session Activity for a Given Time Period

This page covers two related scripts, both built on Active Session History data for a specified time window. The first looks at session activity and provides readable output with username, program, module and SQL text. The second returns the full row detail in Active Session History (ASH) for the same kind of time window. The second script is useful for when the first script does not provide enough detail.

Script 1

Purpose

Lists session activity, joined with username, program, module and the actual SQL text, for a specified time window using Active Session History. It is useful for investigating what was running on the database during a specific period in the past, for example around the time a performance issue or incident was reported.

Using This Script

Use this when investigating a past event and you need to see, not just that a session was active, but who it was, what program and module it came from, and what SQL it was running at that time.

What the Script Shows

Sample time
Username
Program and module
SQL text of the statement active at that sample

Variables

Running the script will prompt for:

s_time - start of the time window, in the format '22/OCT/2014 04:00:00.000'
e_time - end of the time window, in the same format
inst_no - the RAC instance number to filter on (use 1 for a non RAC database)

Note that ASH data is only retained for as long as your AWR retention period allows, so this script can only look back as far as that history extends.

Script 2

Purpose

Returns every column from Active Session History for a specified time window, with no joins or filtering other than the time range. It is useful when you need to inspect detail that the session level script does not provide, such as wait event specifics, session state, or resource consumption details among many other things in the view. I tend to use this script first to get a general overview and then use the first script to drilldown into each session.

UsingThis Script

I tend to use this script first to get a general overview and then use the first script to drilldown into each session. you can also use this when the session level script does not give you enough detail, for example when you need to look at general column information and sourrounding activity, or any of the other diagnostic columns held in ASH that are not provided in the other script. As mentioned above, it is a good starting point for general trouble shooting

Variables

Running the script will prompt for:

s_time: start of the time window, in the format '22/OCT/2014 04:00:00.000'
e_time: end of the time window, in the same format

Best Practice

Because this returns every column, the result set is very wide, so it is best run from a GUI tool such as SQL Developer or Toad rather than SQL*Plus, where the sheer number of columns makes the output unreadable.

Published on

Published 25th September 2026

Page updated

Google Sites

Report abuse