How to find number of user connections to an Oracle database between a specific time period?

Question

I want to find how many users established connections to the oracle 11g database in a 10 minute time interval. Please help

Answer 1

That is what we have auditing for. Enable auditing, for example:

alter system set audit_trail=db scope=spfile;

You need to restart the database for this setting to take effect.

Audit logins:

audit create session;

If you created your database with DBCA, both the above are automatically enabled in 11.2.

Then you can query and browse logins in DBA_AUDIT_SESSION.

Answer 2

If the session was active(making SQL call to database) then you may find it in v$active_session_history or dba_hist_active_sess_history(for persistent information) using the following query.

Note: You need licence for this feature.

 SELECT DISTINCT du.username 
 FROM dba_users du JOIN v$active_session_history ash on(du.user_id=ash.user_id) 
 WHERE ash.sample_time>=systimestamp-(0.000694*10);

If its not then you have to track the information yourself.Login information can be found on listener log file as well, you can grep required inf from there. You can use AFTER LOGON trigger and store the required information.

Or use database audit, If the auditing was enabled(should be enabled if you have created the database using DBCA as Balazs Said in his answer)-

SELECT username FROM dba_audit_session
WHERE
timestamp>=SYSTIMESTAMP-(0.000694*10);

• Raj: OP needs to remember that not all sessions will be captured in ASH, plus you need license to access that data. Perhaps a quick analysis on listener.log or db audit will be much more reliable?