DBA_HIST_ACTIVE_SESS_HISTORY only captures information on sessions that are
active, either on CPU or waiting for something. It does not capture idle
sessions. So I am not sure you can get what you were originally looking for (a
count of all connected sessions) using this view.
auditing is the only way I know of to get such information.
Ram
From: Luis Santos
Sent: Wednesday, July 27, 2016 8:32 AM
Cc: ORACLE-L
Subject: Re: Getting Oracle connected sessions in the past
Craig, thanks a lot! That was a good match! I was only thinking that, as
DBA_HIST_ACTIVE_SESS_HISTORY was a much more fine granule than
DBA_HIST_SNAPSHOT (default 10s versus default 1 hour), maybe a version with a
delta between sample_time from DBA_HIST_ACTIVE_SESS_HISTORY could give us a
richer info.
Anyway I added a lillle cosmetic to your nice script.
col period format a50
break on period skip 1
select dhs.BEGIN_INTERVAL_TIME||' --> '|| dhs.END_INTERVAL_TIME period,
du.username, count(distinct(SESSION_ID||','||SESSION_SERIAL#) ) cnt
from dba_hist_active_sess_history dhash, DBA_HIST_SNAPSHOT dhs, dba_users du
where dhash.user_id = du.user_id
and dhash.snap_id = dhs.snap_id
and dhs.BEGIN_INTERVAL_TIME > trunc(sysdate)
group by dhs.BEGIN_INTERVAL_TIME, dhs.END_INTERVAL_TIME, du.username
order by 1,3
/
--
Att
Luis Santos
2016-07-27 9:15 GMT-03:00 Craig Simpson <craig.simpson@xxxxxxxxxxxxxxxxx>:
Assuming you are licensed for diagnostics...
for a 2 minute job it may do some of what you want but I'm sure you can adapt
it for your own needs
eg change what times you want to report on, this does everything from today.
select dhs.BEGIN_INTERVAL_TIME, dhs.END_INTERVAL_TIME, du.username,
count(distinct(SESSION_ID||','||SESSION_SERIAL#) ) cnt
from dba_hist_active_sess_history dhash, dba_hist_snapshot dhs, dba_users du
where dhash.user_id = du.user_id
and dhash.snap_id = dhs.snap_id
and dhs.BEGIN_INTERVAL_TIME > trunc(sysdate)
group by dhs.BEGIN_INTERVAL_TIME, dhs.END_INTERVAL_TIME, du.username
order by 1,3
BEGIN_INTERVAL_TIME
END_INTERVAL_TIME
USERNAME CNT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
------------------------------ ----------
27-JUL-16 09.00.31.243
27-JUL-16 10.00.12.012
OWF_MGR 2
27-JUL-16 09.00.31.243
27-JUL-16 10.00.12.012
PERFSTAT 1
27-JUL-16 09.00.31.243
27-JUL-16 10.00.12.012
XXXXXXX 3
27-JUL-16 09.00.31.243
27-JUL-16 10.00.12.012 SYS
36
27-JUL-16 09.00.31.243
27-JUL-16 10.00.12.012
SYSMAN 6
27-JUL-16 10.00.12.012
27-JUL-16 11.00.01.601
DBSNMP 6
On 27 July 2016 at 12:59, Luis Santos <lsantos@xxxxxxxxx> wrote:
I was thinking about a smart query on DBA_HIST_ACTIVE_SESS_HISTORY, seeking
for distinct USER_ID column.
I have to be frank: I don´t have the skills to design such a query, and I´m
looking for a ready, already developed one.
--
Att
Luis Santos
2016-07-27 8:54 GMT-03:00 Howard Latham <howard.latham@xxxxxxxxx>:
You can use. If turned on the audit records
On 27 Jul 2016 12:49, "Luis Santos" <lsantos@xxxxxxxxx> wrote:
I was guessing if there´s not an awr table that contains such info. I´m
aware of DBA_HIST_RESOURCE_LIMIT, but this is consolidated info.
--
Att
Luis Santos
2016-07-27 8:39 GMT-03:00 S3v3n11 <neisdsa@xxxxxxxxx>:
dba_audit_session?
On Wed, Jul 27, 2016 at 6:32 AM, Luis Santos <lsantos@xxxxxxxxx>
wrote:
This simple query shows connected users in the moment on an Oracle
instance:
select username, count(*)
from v$session
group by username
order by 2
Is there a way to get this info on a specific time in the past? I
know flashback queries does not work for V$ views (and this is absolutely
reasonable)...
--
Att
Luis Santos
--
Craig Simpson | Velocity Technology Solutions, Ltd.
Database Team
39 Cadogan Street | 5th Floor | Glasgow, G2 7AB
Office: 0141-202-6334 | eMail: craig.simpson@xxxxxxxxxxx | Website
www.velocity.cc
STATEMENT OF CONFIDENTIALITY:
This email may be confidential and/or protected by privilege. If you are not
the intended recipient, disclosure, copying, distribution and/or use are
prohibited; please notify us immediately at counsel@xxxxxxxxxxxxxxxxx and
delete this copy from your system.