Re: Distinct, Active user count

From: De DBA <dedba_at_tpg.com.au>
Date: Tue, 12 Feb 2013 22:57:12 +1000
Message-ID: <511A3C28.7010507_at_tpg.com.au>



On 12/02/13 22:02, Sayan Sergeevich Malakshinov wrote:
> You can try to get it from AWR, but i'm not sure that you have all snaps
> from Jan 1:
>
> select *
> from dba_hist_sysmetric_history
> where upper(metric_name) like '%ACTIV%SESS%';
>
> select *
> from dba_hist_sysmetric_summary
> where upper(metric_name) like '%ACTIV%SESS%';
>
>
> Best regards,
> Sayan Malakshinov
> http://orasql.org
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

If you have the Diagnostic and Tuning pack, of course... ;) Without appropriate license all DBA_HIST% views are out of bounds.

It occurs to me that "Active" in the sense of v$session.status may not quite be what a manager means with "active", i.e. all users that actually log on. To find all users that are logged on, you could use the following:

select count(*)

   from v$session
where username is not null;

This will of course only show what users are logged on at the current time. If you have session auditing enabled, you can count the number of successful logons since 1 January without the need for a D&T pack license from the audit interface:

For number of successful logons per user:

    select username, count(action_name)
      from dba_audit_session
    where action_name = 'LOGON'

       and returncode    = 0
       and timestamp    between to_date( '1-JAN-2013', 'DD-MON-YYYY')
                                and sysdate
     group by username;

For number of distinct users:

    select count(username)

      from ( select distinct username
                 from dba_audit_session
               where action_name = 'LOGON'
                  and returncode    = 0
                  and timestamp    between to_date( '1-JAN-2013', 'DD-MON-YYYY')
                                          and sysdate
            )

The return code in dba_audit_session is the ORA-nnnnn error that an unsuccessful logon attempt received, eg. ORA-1017: invalid username/password. Keep in mind that a number of built-in accounts will log on from time to time to run scheduled jobs, e.g. sysman, dbsnmp.

Hth
Tony

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 12 2013 - 13:57:12 CET

Original text of this message