How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

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

Tablespace Usage History Report

Useful report to show tablespace daily usage and space usage increasing ratio.

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage .

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME                            TYPE     VALUE
------------------------------- -------- -----------------------
control_management_pack_access  string   DIAGNOSTIC+TUNING

The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention.

select THE_DATE,
       TABLESPACE,
       SIZE_IN_GB_TD,
       SIZE_IN_GB_YTD,
       SIZE_IN_GB_TD - SIZE_IN_GB_YTD  SIZE_IN_GB_INCREASED
from ( 
     select 
            to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
            tbl.name TABLESPACE,
            round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
            lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD 
     from 
          dba_hist_snapshot histsnap,
          dba_hist_tbspc_space_usage tblusage,
          v$tablespace tbl,
          v$parameter pmt
    where 
          histsnap.SNAP_ID = tblusage.SNAP_ID
      and tbl.ts# = tblusage.TABLESPACE_ID
      and tbl.name ='&TABLESPACENAME'
      and pmt.name ='db_block_size'
   group by 
          to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
          tbl.name,
          pmt.value
 order by 
         to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
 );

Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'

THE_DATE   TABLESPACE SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB..ASED
---------- ---------- ------------- -------------- -----------------
2017-05-10 USERS        4.17
2017-05-11 USERS        4.17         4.17            0
2017-05-12 USERS        4.45         4.17             .28
2017-05-13 USERS        4.55         4.45             .1
2017-05-14 USERS        4.55         4.55            0
2017-05-15 USERS        4.56         4.55             .01
2017-05-16 USERS        4.84         4.56             .28
2017-05-17 USERS        4.96         4.84             .12
2017-05-18 USERS        5.14         4.96             .18
2017-05-19 USERS        5.14         5.14            0

10 rows selected.

Change AWR Retention and Interval

Use “dbms_workload_repository.modify_snapshot_settings” to change AWR retention and interval.

Check current Retention and Interval

RETENTION = 8 days ( 11520 Mins ) ( Default )
INTERVAL = 1 hour ( 60 Mins ) ( Default )

SQL> desc dba_hist_wr_control
 Name          Null?     Type
 ------------- --------- --------------------------
 DBID          NOT NULL NUMBER
 SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION NOT NULL     INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                VARCHAR2(10)

SQL> select dbid from v$database;

DBID
----------
2920212463

SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL       RETENTION
---------- ------------------- ------------------------------
2920212463 +00000 01:00:00.0   +00008 00:00:00.0

SQL> col SNAP_INTERVAL_MINS format a40
SQL> col RETENTION_MINS format a40
SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
            RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL_MINS            RETENTION_MINS
---------- ----------------------------  -----------------------------
2920212463 +000000060 00:00:00.000000000 +000011520 00:00:00.000000000

Change Retention and Interval

Retention from 8 days (  11520 Mins )  —-> 31 days ( 44640 Mins )
Interval from 1 hour ( 60 Mins ) —-> 0.5 hour ( 30 Mins )

SQL>exec dbms_workload_repository.modify_snapshot_settings
                                 (retention=>44640,
                                  interval=>30,
                                      dbid=>2920212463);

PL/SQL procedure successfully completed.

Check New Retention and Interval

SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

DBID S     NAP_INTERVAL      RETENTION
---------- ----------------- ---------------------
2920212463 +00000 00:30:00.0 +00031 00:00:00.0

SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
                     RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS                   RETENTION_MINS
---------- ------------------------------- ----------------------------
2920212463 +000000030 00:00:00.000000000   +000044640 00:00:00.000000000

Check SYSAUX Tablespace Usage

SQL> SELECT occupant_name, space_usage_kbytes/1024 MBS 
       FROM V$SYSAUX_OCCUPANTS 
   order by 2;

OCCUPANT_NAME             MBS
------------------------- ----------
TSM                         0
EXPRESSION_FILTER           0
ULTRASEARCH_DEMO_USER       0
...
..
.
SM/ADVISOR                 69.3125
AUDIT_TABLES              136.1875
SM/OPTSTAT                406.4375
SM/AWR                   1633.3125

31 rows selected.