SNIPED sessions and ORA-00020: maximum number of processes (%s) exceeded

When you implement the resource limit, the sessions that exceed the IDLE limit is marked as SNIPED in V$SESSION view and you may get “ORA-00020: maximum number of processes (%s) exceeded” error because Oracle doesn’t kill that session in OS level and it assumes it as a “process”. So for this, you need to kill those sessions manually


Here I show a little demonstration of the whole process: – First of all, set the RESOURCE_LIMIT parameter to TRUE to enforce the resource limit in database profiles

1 2 3 4 5 6 7

SQL> show parameter resource_limit NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ resource_limit                       boolean     FALSE   SQL> alter system set resource_limit=true; System altered.

- Then create a profile and set IDLE_TIME  to 1 minute:

1 2 3

SQL> create profile test_profile limit   2  idle_time 1; Profile created.

- Create a user and assign the profile to that user:

1 2 3 4 5

SQL> grant dba to usr identified by usr; Grant succeeded.   SQL> alter user usr profile test_profile; User altered.

- Change the PROCESSES parameter to make the maximum number of operating system processes lower

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> show parameter process NAME                                 TYPE        VALUE processes                            integer     150   SQL> alter system set processes=25 scope=spfile; System altered.   SQL> startup force   SQL> show parameter processes NAME                                 TYPE        VALUE processes                            integer     25   SQL> select count(1) from v$process;     COUNT(1) ----------         22

Now open two different terminals and connect to the database with USR user:

1	sqlplus usr/usr

Check the view V$PROCESS. It should be 24

1 2 3 4 5

SQL> select count(1) from v$process;     COUNT(1) ----------         24

Now open third terminal and try to connect to the database with the user USR.You will get an error because the count of the processes will reach the limit:

1 2 3 4 5 6 7 8

[oracle@localhost ~]$ sqlplus usr/usr   ERROR: ORA-00020: maximum number of processes (%s) exceeded   Enter user-name:   SQL>

Now wait for a minute to reach the limit of the IDLE_LIMIT resource (we’ve set it to 1 minute) and query the SYSDATE from any USR session:

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select s.status, count(1), s.username from v$process p, v$session s where paddr(+)=addr group by s.status, s.username order by 1;   STATUS     COUNT(1) USERNAME -------- ---------- ------------------------------ ACTIVE            1 SYS ACTIVE           16 INACTIVE          2 SYS SNIPED            2 USR                   3

That’s the issue. If you try to connect to the database, you’ll get ORA-00020 error again. Please note that SNIPED doesn’t mean that it’s KILLED. It is not either killed, nor active. The user is not able to run any query, however it holds a process on OS level:

1 2 3 4 5

SQL> select count(1) from v$process;     COUNT(1) ----------         24

Run any query with already connected (and SNIPED) USR user. You’ll get the following error:

1 2 3 4 5 6

SQL> select sysdate from dual; select sysdate from dual * ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again SQL>

Now query V$SESSION and V$PROCESS views again:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select s.status, count(1), s.username from v$process p, v$session s where paddr(+)=addr group by s.status, s.username order by 1;   STATUS     COUNT(1) USERNAME -------- ---------- ------------------------------ ACTIVE            1 SYS ACTIVE           16 INACTIVE          2 SYS SNIPED            1 USR                   4   SQL> select count(1) from v$process;     COUNT(1) ----------         24

The process will be free only when you “exit” from Sql*Plus. Exit from the session that you got an error and query V$PROCESS again:

1 2 3 4 5

SQL> select count(1) from v$process;     COUNT(1) ----------         23

To kill the SNIPED sessions you have two options. The first option is to run ALTER SYSTEM KILL SESSION command. For this you need to get SID and SERIAL# of the sniped session.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> select sid, s.serial#, status from v$process p, v$session s where paddr=addr and s.username='USR';          SID    SERIAL# STATUS ---------- ---------- --------          9         10 SNIPED   SQL> alter system kill session '9,10' immediate; System altered.   SQL> select s.status, count(1), s.username from v$process p, v$session s where paddr(+)=addr group by s.status, s.username order by 1;   STATUS     COUNT(1) USERNAME -------- ---------- ------------------------------ ACTIVE            1 SYS ACTIVE           16 INACTIVE          2 SYS KILLED            1 USR                   3

After some seconds you’ll see that the session is cleared from both views:

1 2 3 4 5 6 7 8

SQL> /   STATUS     COUNT(1) USERNAME -------- ---------- ------------------------------ ACTIVE            1 SYS ACTIVE           16 INACTIVE          2 SYS                   3

However, due to some bugs, sometimes you may not get the sessions killed using ALTER SYSTEM KILL SESSSION command. For this, you need to kill the process from OS level.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> select spid, status from v$process p, v$session s where paddr=addr and s.username='USR';   SPID         STATUS ------------ -------- 2795         SNIPED   [oracle@localhost ~]$ kill -9 2795   SQL> select spid, status from v$process p, v$session s where paddr=addr and s.username='USR';   no rows selected   SQL>

Run any sql command on the killed session:

1 2 3 4 5 6 7

SQL> select sysdate from dual; select sysdate from dual * ERROR at line 1: ORA-03135: connection lost contact   SQL>

(For more information on killing sniped sessions, refer to MOS 96170.1)