Re: Getting Oracle connected sessions in the past

But isn't that what aud does
On 27 Jul 2016 20:30, "Andrew Kerber" <andrew.kerber_at_gmail.com> wrote:

> I have seen places that required this information for auditing purposes
> set up a log on and log off trigger that posts the times and other
> information to a log table.
>
> On Wed, Jul 27, 2016 at 1:17 PM, Franck Pachot <franck_at_pachot.net> wrote:
>
>> Hi,
>> If you add the DB time (active sessions) and the SQL*Net message from
>> client (inactive ones) and divide by elapsed time then you have an average
>> of user sessions connected.
>> You can check the session logons statistic to see if they are roughly
>> constant over the time window.
>> Regards,
>> Franck
>> Le mer. 27 juil. 2016 à 15:03, Ram Cheruvattath <
>> ram.cheruvattath_at_gmail.com> a écrit :
>>
>>> 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 <lsantos_at_pobox.com>
>>> *Sent:* Wednesday, July 27, 2016 8:32 AM
>>> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
>>> *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 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.
>>>>
>>>
>>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

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