Oracle Kill Session Command Fails with ORA-00027: cannot kill current session

Error Description:
DBA is trying to kill oracle sessions and one session is getting failed with following error.
SQL> alter system kill session '786,53315';
alter system kill session '786,53315'
*
ERROR at line 1:
ORA-00027: cannot kill current session

Solution Description:
Error is because you are trying to kill your own session which is not allowed. You are going to cut the branch where you are which is logically false. But there are ways to kill your session by killing the OS process. Please see the steps.

SQL> alter system kill session '786,53315';
alter system kill session '786,53315'
*
ERROR at line 1:
ORA-00027: cannot kill current session

Find out your session id using following query. If you are running on RAC database use gv$session otherwise use v$session.

SQL> select username,inst_id, sid, serial# FROM gV$SESSION WHERE audsid = userenv('sessionid');

USERNAME                          INST_ID        SID    SERIAL#
------------------------------ ---------- ---------- ----------
PRODDB                                  2        786      53315

Find out the OS process id(SPID) using following query.
SQL> select s.sid, s.serial#, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid = 786;
       SID    SERIAL# PROGRAM                                          SPID
---------- ---------- ------------------------------------------------ ------------------------
       786      53315 sqlplus (TNS V 25810

Kill the process using OS command.
SQL> !kill -9 25810

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 25810
Session ID: 786 Serial number: 53315
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google