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 profilesSQL> 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:SQL> create profile test_profile limit
2 idle_time 1;
Profile created.
- Create a user and assign the profile to that user: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 lowerSQL> 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:sqlplus usr/usr
Check the view V$PROCESS. It should be 24SQL> 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:[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: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: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: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: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: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.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: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.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:SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
SQL>