In an effort to set a reasonable sessions_per_user profile setting for shared database accounts or other non-unique account (ie. an account an application uses to log into the database for all app user activity) I'm wondering if there's a view to look at or a script someone has created to see the maximum number of simultaneous sessions a user has had over some defined period of time.

Note: some database are 11.2g and others are 19c if that makes a difference, hence the different version tags.

  • I don't believe there's any kind of high-water-mark for that in the dictionary. I've queried v$session (or gv$session) in the past and grouped by username to take a periodic poll. If you really needed to capture this at regular intervals over time you could have a scheduled job insert the data into a table with a timestamp.
    – pmdba
    Nov 19 '21 at 16:33
  • Are you auditing logins and logouts (should be the default behavior in 19c but not in 11.2)? If so, you could potentially query the audit trail. Are you licensed to use the ASH? You could probably cobble together a reasonable approximation from dba_hist_active_sess_history to see the distinct session_id, session_serial# rows that had some activity over different buckets of time for each user. Imperfect but it probably gets you to a reasonable guess assuming you're going to apply a decent fudge factor on top of that when setting the profile. Nov 19 '21 at 17:31
  • I can try something based on those suggestions. I'll post back if I come up with something that's useful enough. Nov 20 '21 at 20:14
  • Not particularly important but there's no "11.2g". "11g" and "19c" are product brand names, while 11.2, 19.8 etc are versions (or use four digits like if you want to be super specific). Nov 23 '21 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.