SNIPED sessions and ORA-00020: maximum number of processes (%s) exceeded
Posted by Kamran Agayev A. on December 14th, 2010
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
[sourcecode]
SQL> show parameter resource_limit
NAME TYPE VALUE
———————————— ———– ——————————
resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
System altered.
[/sourcecode]
– Then create a profile and set IDLE_TIME to 1 minute:
[sourcecode]SQL> create profile test_profile limit
2 idle_time 1;
Profile created.
[/sourcecode]
– Create a user and assign the profile to that user:
[sourcecode]
SQL> grant dba to usr identified by usr;
Grant succeeded.
SQL> alter user usr profile test_profile;
User altered.
[/sourcecode]
– Change the PROCESSES parameter to make the maximum number of operating system processes lower
[sourcecode]
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
[/sourcecode]
Now open two different terminals and connect to the database with USR user:
[sourcecode]sqlplus usr/usr[/sourcecode]
Check the view V$PROCESS. It should be 24
[sourcecode]
SQL> select count(1) from v$process;
COUNT(1)
———-
24
[/sourcecode]
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:
[sourcecode]
[oracle@localhost ~]$ sqlplus usr/usr
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Enter user-name:
SQL>
[/sourcecode]
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:
[sourcecode]
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
[/sourcecode]
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:
[sourcecode]
SQL> select count(1) from v$process;
COUNT(1)
———-
24
[/sourcecode]
Run any query with already connected (and SNIPED) USR user. You’ll get the following error:
[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL>
[/sourcecode]
Now query V$SESSION and V$PROCESS views again:
[sourcecode]
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
[/sourcecode]
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:
[sourcecode]
SQL> select count(1) from v$process;
COUNT(1)
———-
23
[/sourcecode]
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.
[sourcecode]
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
[/sourcecode]
After some seconds you’ll see that the session is cleared from both views:
[sourcecode]
SQL> /
STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
3
[/sourcecode]
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.
[sourcecode]
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>
[/sourcecode]
Run any sql command on the killed session:
[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
SQL>
[/sourcecode]
(For more information on killing sniped sessions, refer to MOS 96170.1)
December 14th, 2010 at 10:09 pm
Hi Kamran,I like Your posts and blog info especially Interviews with experts.
Here I would like you to write some more info when you are posting any blog along with the actual Content!
Like What background process is responsible for this sniped Status?
(“PMON is responsible for sniping the sessions” and all the resources(& locks) will be released even though the session still exists in v$session as sniped)
I think this kind of way will help the beginers
Keep posting! Have a good day
Sriram
December 15th, 2010 at 10:14 am
Hi Sriram and thank you for update
Actually we can talk more and more about SPINED sessions, but in this post I mostly focused on “SPINED sessions and ORA-00020 error” as a whole
December 15th, 2010 at 7:17 pm
Its just a comment nothing more than that.
And it is “SNIPED” not “SPINED”
Sriram
December 16th, 2010 at 12:36 am
It’s typo Sriram