OraFAQ Forum: Server Administration » sessions established by each user per hour

Home » RDBMS Server » Server Administration » sessions established by each user per hour (oracle 11.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sessions established by each user per hour [message #645389]

Fri, 04 December 2015 15:56

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.

Report message to a moderator

Re: sessions established by each user per hour [message #645392 is a reply to message #645389]

Fri, 04 December 2015 17:47

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: sessions established by each user per hour [message #645405 is a reply to message #645389]

Sat, 05 December 2015 00:16

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: sessions established by each user per hour [message #645488 is a reply to message #645389]

Mon, 07 December 2015 08:55

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

Report message to a moderator

Re: sessions established by each user per hour [message #645491 is a reply to message #645488]

Mon, 07 December 2015 09:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

AUDIT CREATE SESSION will provide accurate COUNT() results.

Report message to a moderator

Re: sessions established by each user per hour [message #645502 is a reply to message #645389]

Mon, 07 December 2015 10:52

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

Report message to a moderator

Re: sessions established by each user per hour [message #645503 is a reply to message #645502]

Mon, 07 December 2015 11:00

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Will the following query help me accomplish the result?

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

Report message to a moderator

Re: sessions established by each user per hour [message #645545 is a reply to message #645502]

Tue, 08 December 2015 06:39

EdStevens
Messages: 1376
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?

Report message to a moderator

Re: sessions established by each user per hour [message #645547 is a reply to message #645545]

Tue, 08 December 2015 07:58

PBG789
Messages: 7
Registered: December 2015

Junior Member

Yes,we have it.

Report message to a moderator

Re: sessions established by each user per hour [message #645549 is a reply to message #645547]

Tue, 08 December 2015 08:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

you need to query DBA_AUDIT_SESSION

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 11:49:57 CST 2025