Re: Getting Oracle connected sessions in the past

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