Re: Finding concurrent users at noon

Stefan Schindler wrote:
> I have to know how many users were connected to a database at every full
> hour.
> With the following select ...
> SELECT
> to_char(TIMESTAMP, 'YYYY') YYYY,
> to_char(TIMESTAMP, 'MM') MM,
> to_char(TIMESTAMP, 'DD') DD,
> to_char(TIMESTAMP, 'HH24') HH, -- or LOGOFF_TIME
> count(TIMESTAMP) LOGINS
> FROM DBA_AUDIT_SESSION
> WHERE TIMESTAMP >= to_date('03.01.1997 07:00:00', 'DD.MM.YYYY HH24:MI:SS')
> AND TIMESTAMP < to_date('03.01.1997 19:00:00', 'DD.MM.YYYY HH24:MI:SS')
> GROUP BY
> to_char(TIMESTAMP, 'YYYY'),
> to_char(TIMESTAMP, 'MM'),
> to_char(TIMESTAMP, 'DD'),
> to_char(TIMESTAMP, 'HH24');
>
> ... I only get the number of users, that did login/logoff in the time
> between HH:00:00 and HH:59:59.
>
> What helps?

DBA_AUDIT_SESSION records all connect/disconnect actions on the database, and that is what you get from your query.

If you want to monitor number of connected users at any moment, query from v$SESSION:

    SELECT sysdate, COUNT(username) FROM v$session;

Now, if you want to record this number at every full hour, create a table for storing results and insert the output from the query. To fire this insert every hour, you can use DBMS_JOB or OS utilities (cron, at,..).

Regards,

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================