Home » RDBMS Server » Server Administration » sessions established by each user per hour (oracle 11.2.0.4 on Linux)
sessions established by each user per hour [message #645389] Fri, 04 December 2015 15:56 Go to next message
PBG789
Messages: 7
Registered: December 2015
Junior Member
Hello all

I am trying to find the total number of sessions established by certain users by each hour.(up to last 24 hours)

Lets say I have application users A,B and C. How can I find the total number of sessions each user established per hour?

I checked v$session, but it only provides current sessions, I am more looking into history of the sessions established per hour.

Any help will be greatly appreciated.


Thanks.

Re: sessions established by each user per hour [message #645392 is a reply to message #645389] Fri, 04 December 2015 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 26185
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Is application 3-tier? If so, application must provide these details.
Is connection pooling used? If so, application must provide these details.

If none of the above, you need to enable AUDIT CREATE SESSION. Google is your friend.

Re: sessions established by each user per hour [message #645405 is a reply to message #645389] Sat, 05 December 2015 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In 11.2 CREATE SESSION is audited by default if you have set audit_trail configuration parameter to something difference from NONE (its default value).
If this is your case then you can query DBA_AUDIT_SESSION view.

Re: sessions established by each user per hour [message #645488 is a reply to message #645389] Mon, 07 December 2015 08:55 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

As the others have said, you can audit CREATE SESSION. I often find that this doesn't give me enough info on the session, so I also have an AFTER LOGON trigger, which dumps info from V$SESSION into a table that I can query later.


HTH,
Brian
Re: sessions established by each user per hour [message #645491 is a reply to message #645488] Mon, 07 December 2015 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 26185
Registered: January 2009
Location: SoCal
Senior Member
AUDIT CREATE SESSION will provide accurate COUNT() results.
Re: sessions established by each user per hour [message #645502 is a reply to message #645389] Mon, 07 December 2015 10:52 Go to previous messageGo to next message
PBG789
Messages: 7
Registered: December 2015
Junior Member
Thank you all for the response.

Will the following query help me accomplish the result? In the query below I am trying to see how many sessions were created by the application schemas in the DB in the last 60 min.

select USER_ID, count(*) from v$active_session_history where SAMPLE_TIME > SYSDATE -60/(24*60) group by USER_ID;

USER_ID COUNT(*)
---------- ----------
78 45672
30 233
194 4
223 113
215 1
74 32583
75 1242
0 8429
79 740
Re: sessions established by each user per hour [message #645503 is a reply to message #645502] Mon, 07 December 2015 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Will the following query help me accomplish the result?


No.
And format your query as BlackSwan already told you.

Re: sessions established by each user per hour [message #645545 is a reply to message #645502] Tue, 08 December 2015 06:39 Go to previous messageGo to next message
EdStevens
Messages: 979
Registered: September 2013
Senior Member
PBG789 wrote on Mon, 07 December 2015 10:52


select USER_ID, count(*) from v$active_session_history


Do you have the necessary extra cost Diagnostics Pack license required to access v$active_session_history?
Re: sessions established by each user per hour [message #645547 is a reply to message #645545] Tue, 08 December 2015 07:58 Go to previous messageGo to next message
PBG789
Messages: 7
Registered: December 2015
Junior Member
Yes,we have it.
Re: sessions established by each user per hour [message #645549 is a reply to message #645547] Tue, 08 December 2015 08:06 Go to previous message
BlackSwan
Messages: 26185
Registered: January 2009
Location: SoCal
Senior Member
you need to query DBA_AUDIT_SESSION
Previous Topic: Why isn't registry$history updated with latest 12.1.0.2
Next Topic: Unable to connect via wallet credentials - ORA-12534: TNS:operation not supported
Goto Forum:
  


Current Time: Tue Oct 16 08:12:36 CDT 2018