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 a50break on period skip 1select
dhs.BEGIN_INTERVAL_TIME||' --> '|| dhs.END_INTERVAL_TIME period,
du.username, count(distinct(SESSION_ID||','||SESSION_SERIAL#) ) cntfrom
dba_hist_active_sess_history dhash, DBA_HIST_SNAPSHOT dhs, dba_users
duwhere dhash.user_id = du.user_idand dhash.snap_id = dhs.snap_idand
dhs.BEGIN_INTERVAL_TIME > trunc(sysdate)group by dhs.BEGIN_INTERVAL_TIME,
dhs.END_INTERVAL_TIME, du.usernameorder by 1,3/*
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 usernameorder 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
<https://www.google.com/maps/preview#!q=39+Cadogan+Street+%7C+5th+Floor+%7C+Glasgow%2C+G2+7AB&data=!4m15!2m14!1m13!1s0x4888469d042c2e69%3A0xe50daa21f9160909!3m8!1m3!1d8547!2d-73.9703852!3d40.7568975!3m2!1i1254!2i543!4f13.1!4m2!3d55.8598357!4d-4.2625208>
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.
