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

ORA-Errors

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
[[email protected] ~]$ 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

[[email protected] ~]$ 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>

Leave a Reply

Your email address will not be published. Required fields are marked *