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
    Commented Nov 19, 2021 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. Commented Nov 19, 2021 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. Commented Nov 20, 2021 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). Commented Nov 23, 2021 at 23:23


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.