Oracle – How to Find Number of Sessions Connected to Database

oracle

I need to know how many sessions was connected in a period of time for each snapshot. Is this possible in Oracle?

Thanks

Best Answer

You can find this information in DBA_HIST_RESOURCE_LIMIT. Using this view requires the Diagnostic Pack option.

For example, sessions in the last 1 day:

select
  s.instance_number,
  s.end_interval_time,
  rn.current_utilization
from
  dba_hist_snapshot s
  join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)
where
  rn.resource_name = 'sessions'
  and s.end_interval_time > sysdate - 1
order by
  s.end_interval_time
;

INSTANCE_NUMBER END_INTERVAL_TIME         CURRENT_UTILIZATION
--------------- ------------------------- -------------------
              1 07-JUL-16 03.00.23.658 PM                  74
              1 07-JUL-16 04.00.25.330 PM                  74
              1 07-JUL-16 05.00.27.072 PM                  74
              1 07-JUL-16 06.00.28.807 PM                  73
              1 07-JUL-16 07.00.30.589 PM                  73
              1 07-JUL-16 08.00.32.354 PM                  73
              1 07-JUL-16 09.00.34.187 PM                  73
              1 07-JUL-16 10.00.36.100 PM                  79
              1 07-JUL-16 11.00.38.061 PM                  73
              1 08-JUL-16 12.00.39.854 AM                  73
              1 08-JUL-16 01.00.41.673 AM                  73
              1 08-JUL-16 02.00.43.523 AM                  75
              1 08-JUL-16 03.00.45.355 AM                  73
              1 08-JUL-16 04.00.47.141 AM                  73
              1 08-JUL-16 05.00.48.895 AM                  73
              1 08-JUL-16 06.00.50.639 AM                  73
              1 08-JUL-16 07.00.52.367 AM                  73
              1 08-JUL-16 08.00.54.128 AM                  73
              1 08-JUL-16 09.00.55.850 AM                  73
              1 08-JUL-16 10.00.57.572 AM                  73
              1 08-JUL-16 11.00.59.295 AM                  73
              1 08-JUL-16 12.00.00.949 PM                  73
              1 08-JUL-16 01.00.02.704 PM                  73
              1 08-JUL-16 02.00.04.485 PM                  73
Related Question