July 4, 2015

ORA-20 Maximum number of processes(150) exceeded

SQL> select status,count(*) from v$session group by status;

STATUS     COUNT(*)
-------- ----------
ACTIVE           43
INACTIVE        354

SQL> select * from v$resource_limit where resource_name in ('sessions','processes');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION             LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------------ ------------------------------
processes                                      403             435        500                            500
sessions                                       421             530        792                            792

SQL> select username,status,count(*) from v$session group by username,status;

USERNAME                       STATUS     COUNT(*)
------------------------------ -------- ----------
PROD_101                INACTIVE         27
CRM_USER                        INACTIVE          3
SYS                            ACTIVE            1
ORDM                            INACTIVE          1
DBSNMP                         INACTIVE          2
ORDM_TEST                     INACTIVE         38

KZAENGER                       INACTIVE          4
RTALBOOM                       INACTIVE          1

CPINEDA                        INACTIVE          1
GROMERO                        INACTIVE          2
ORDM_UAT                       INACTIVE        275
PUBLIC                         INACTIVE          1

12 rows selected.

SQL> select count(*) from v$session where last_call_et>3600 and username='ORDM_UAT';

  COUNT(*)
----------
202


From the above result we can understand that there are 202 sessions are in inactive state more than half an hour.
If we kill the sessions then the issue will resolved (But confirm with the application team before killing the sessions)

No comments:

Post a Comment