1)From V$LICENSE View. It shows USER sessions only excluding database system sessions.
SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license; SESSIONS_CURRENT SESSIONS_HIGHWATER ---------------- ------------------ 771 1346 SESSIONS_CURRENT : Current number of concurrent user sessions SESSIONS_HIGHWATER: Highest number of concurrent user sessions since the instance started.
2) From v$resource_limit view. It shows both users and database system sessions.
SQL> select
RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE
from
v$resource_limit
where
RESOURCE_NAME in ('processes','sessions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------- ------------------- --------------- ---------------
processes 842 1414 4040
sessions 865 1439 6096
CURRENT_UTILIZATION : Number of (resources, locks, or processes)
currently being used.
MAX_UTILIZATION : Maximum consumption of this resource since
the last instance start-up
3)From AWR.
col STARTT format a20
col FINISHH format a20
col RESOURCE_NAME format a13
SQL>select
a.snap_id SNAP_ID,
to_char(b.begin_interval_time,'YYYYMMDD-HH24:MI:SS') STARTT,
to_char(b.end_interval_time,'YYYYMMDD-HH24:MI:SS') FINISHH,
a.resource_name,
a.CURRENT_UTILIZATION,
a.max_utilization ,
a.INITIAL_ALLOCATION,
a.LIMIT_VALUE
from
DBA_HIST_RESOURCE_LIMIT a,
DBA_HIST_SNAPSHOT b
where
( a.resource_name like '%process%' or a.resource_name like '%session%')
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and a.instance_number=1
and b.begin_interval_time > sysdate - 2
order by
a.snap_id,
a.resource_name;
SNAP_ID STARTT FINISHH RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- -------------------- -------------------- ------------- ------------------- --------------- ---------- ----------
63141 20171214-10:00:13 20171214-11:00:15 processes 845 1414 4040 4040
63141 20171214-10:00:13 20171214-11:00:15 sessions 869 1439 6096 6096
63142 20171214-11:00:15 20171214-12:00:18 processes 847 1414 4040 4040
63142 20171214-11:00:15 20171214-12:00:18 sessions 871 1439 6096 6096
63143 20171214-12:00:18 20171214-13:00:21 processes 845 1414 4040 4040
63143 20171214-12:00:18 20171214-13:00:21 sessions 870 1439 6096 6096
63144 20171214-13:00:21 20171214-14:00:23 processes 842 1414 4040 4040
63144 20171214-13:00:21 20171214-14:00:23 sessions 866 1439 6096 6096
63145 20171214-14:00:23 20171214-15:00:26 processes 843 1414 4040 4040
63145 20171214-14:00:23 20171214-15:00:26 sessions 869 1439 6096 6096
63146 20171214-15:00:26 20171214-16:00:29 processes 852 1414 4040 4040
63146 20171214-15:00:26 20171214-16:00:29 sessions 879 1439 6096 6096
63147 20171214-16:00:29 20171214-17:00:32 processes 849 1414 4040 4040
63147 20171214-16:00:29 20171214-17:00:32 sessions 873 1439 6096 6096
63148 20171214-17:00:32 20171214-18:00:35 processes 848 1414 4040 4040
63148 20171214-17:00:32 20171214-18:00:35 sessions 872 1439 6096 6096
63149 20171214-18:00:35 20171214-19:00:02 processes 840 1414 4040 4040
63149 20171214-18:00:35 20171214-19:00:02 sessions 864 1439 6096 6096
63150 20171214-19:00:02 20171214-20:00:05 processes 841 1414 4040 4040
63150 20171214-19:00:02 20171214-20:00:05 sessions 865 1439 6096 6096
63151 20171214-20:00:05 20171214-21:00:08 processes 845 1414 4040 4040
63151 20171214-20:00:05 20171214-21:00:08 sessions 871 1439 6096 6096
63152 20171214-21:00:08 20171214-22:00:12 processes 850 1414 4040 4040
63152 20171214-21:00:08 20171214-22:00:12 sessions 875 1439 6096 6096
63153 20171214-22:00:12 20171214-23:00:15 processes 854 1414 4040 4040
63153 20171214-22:00:12 20171214-23:00:15 sessions 880 1439 6096 6096
63154 20171214-23:00:15 20171215-00:00:17 processes 849 1414 4040 4040
63154 20171214-23:00:15 20171215-00:00:17 sessions 873 1439 6096 6096
63155 20171215-00:00:17 20171215-01:00:21 processes 851 1414 4040 4040
63155 20171215-00:00:17 20171215-01:00:21 sessions 877 1439 6096 6096
63156 20171215-01:00:21 20171215-02:00:24 processes 837 1414 4040 4040
63156 20171215-01:00:21 20171215-02:00:24 sessions 861 1439 6096 6096
63157 20171215-02:00:24 20171215-03:00:27 processes 847 1414 4040 4040
63157 20171215-02:00:24 20171215-03:00:27 sessions 872 1439 6096 6096
63158 20171215-03:00:27 20171215-04:00:01 processes 846 1414 4040 4040
63158 20171215-03:00:27 20171215-04:00:01 sessions 872 1439 6096 6096
63159 20171215-04:00:01 20171215-05:00:04 processes 841 1414 4040 4040
63159 20171215-04:00:01 20171215-05:00:04 sessions 865 1439 6096 6096
63160 20171215-05:00:04 20171215-06:00:06 processes 839 1414 4040 4040
63160 20171215-05:00:04 20171215-06:00:06 sessions 864 1439 6096 6096
63161 20171215-06:00:06 20171215-07:00:09 processes 847 1414 4040 4040
63161 20171215-06:00:06 20171215-07:00:09 sessions 871 1439 6096 6096
63162 20171215-07:00:09 20171215-08:00:12 processes 843 1414 4040 4040
63162 20171215-07:00:09 20171215-08:00:12 sessions 866 1439 6096 6096
63163 20171215-08:00:12 20171215-09:00:14 processes 852 1414 4040 4040
63163 20171215-08:00:12 20171215-09:00:14 sessions 878 1439 6096 6096
46 rows selected.
4) From OEM Repository.
col COLUMN_LABEL format a30
col MINIMUM format 99.99
col MAXIMUM format 99.99
col AVERAGE format 99.99
SQL>select
to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL,
MINIMUM,MAXIMUM,AVERAGE
from MGMT$METRIC_DAILY
where TARGET_NAME='RACTEST'
and COLUMN_LABEL in ('Session Limit Usage (%)')
order by
ROLLUP_TIMESTAMP,
COLUMN_LABEL;
TO_CHAR(RO COLUMN_LABEL MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Session Limit Usage (%) 2.87 6.40 5.22
2017-07-16 Session Limit Usage (%) 6.45 14.57 13.67
2017-07-17 Session Limit Usage (%) 7.96 14.40 14.07
2017-07-18 Session Limit Usage (%) 13.99 14.71 14.34
2017-07-19 Session Limit Usage (%) 14.14 14.94 14.63
2017-07-20 Session Limit Usage (%) 14.55 15.04 14.80
...
..
.
2017-12-04 Session Limit Usage (%) 14.88 15.88 15.47
2017-12-05 Session Limit Usage (%) 14.45 15.16 14.80
2017-12-06 Session Limit Usage (%) 13.99 14.98 14.58
2017-12-07 Session Limit Usage (%) 13.86 14.32 14.09
2017-12-08 Session Limit Usage (%) 13.88 14.37 14.12
2017-12-09 Session Limit Usage (%) 13.80 14.40 14.04
2017-12-10 Session Limit Usage (%) 13.91 14.44 14.06
2017-12-11 Session Limit Usage (%) 13.88 14.35 14.07
2017-12-12 Session Limit Usage (%) 13.96 14.30 14.12
2017-12-13 Session Limit Usage (%) 13.98 14.44 14.18
2017-12-14 Session Limit Usage (%) 14.09 14.42 14.24
--
--
SQL> select
to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL,
MINIMUM,MAXIMUM,AVERAGE
from MGMT$METRIC_DAILY
where TARGET_NAME='RACTEST'
and COLUMN_LABEL in ('Process Limit Usage (%)')
order by ROLLUP_TIMESTAMP,
COLUMN_LABEL;
TO_CHAR(RO COLUMN_LABEL MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Process Limit Usage (%) 3.76 9.16 7.33
2017-07-16 Process Limit Usage (%) 9.21 21.31 20.00
2017-07-17 Process Limit Usage (%) 11.34 21.09 20.60
2017-07-18 Process Limit Usage (%) 20.50 21.49 21.00
2017-07-19 Process Limit Usage (%) 20.74 21.83 21.44
2017-07-20 Process Limit Usage (%) 21.36 22.00 21.71
...
..
.
2017-12-09 Process Limit Usage (%) 20.25 20.99 20.58
2017-12-10 Process Limit Usage (%) 20.40 21.16 20.61
2017-12-11 Process Limit Usage (%) 20.37 21.01 20.63
2017-12-12 Process Limit Usage (%) 20.47 20.97 20.70
2017-12-13 Process Limit Usage (%) 20.52 21.06 20.78
2017-12-14 Process Limit Usage (%) 20.72 21.11 20.89
Make DBA Life Easy 