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 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/*

*--*
*Att*

*Luis Santos*

2016-07-27 9:15 GMT-03:00 Craig Simpson <craig.simpson_at_velocitycloud.com>:

> 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_at_pobox.com> 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_at_gmail.com>:
>>
>>> You can use. If turned on the audit records
>>> On 27 Jul 2016 12:49, "Luis Santos" <lsantos_at_pobox.com> 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_at_gmail.com>:
>>>
>>>> dba_audit_session?
>>>>
>>>> On Wed, Jul 27, 2016 at 6:32 AM, Luis Santos <lsantos_at_pobox.com> 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_at_velocity.cc* | 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_at_velocitycloud.com and
> delete this copy from your system.
>

--
http://www.freelists.org/webpage/oracle-l