Re: Distinct, Active user count
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-lReceived on Tue Feb 12 2013 - 13:57:12 CET