ORA-00027 cannot kill current session oracle solution and commands

Oracle error ORA-00027: cannot kill current session typically occurs when you attempt to terminate (kill) the session that you are currently connected to. Oracle does not allow you to kill the session you're using; instead, you can only kill other sessions.

Check Current Session To avoid this error, first check if you are trying to kill the session you're using. You can check your session information with the following query:

SELECT sid, serial#, username, status
FROM v$session
WHERE audsid = USERENV('SESSIONID');

This will return the SID (Session ID) and Serial# of your current session. Ensure that you are not targeting your own session in subsequent kill commands.

Kill Another Session To kill another session, you need to identify the session first and then kill it using the following steps:

Identify the target session:

SELECT sid, serial#, username, status
FROM v$session
WHERE username = 'USER_TO_KILL';
Kill the session:

After identifying the SID and Serial# of the target session, use this command to kill it:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Example:

ALTER SYSTEM KILL SESSION '123,4567';

If the session doesn't terminate right away, you can add the IMMEDIATE keyword:

ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

 

Killing Sessions on RAC (Real Application Clusters) If you are working on a Real Application Cluster (RAC), you may need to specify the INST_ID from GV$SESSION. In that case, use:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@INST_ID';

 

Force Session Kill Using OS Command If the session is still not killed after using ALTER SYSTEM KILL SESSION, you may need to forcefully terminate the session at the operating system level. First, get the session's process ID (spid) from the following query:

SELECT p.spid, s.sid, s.serial#
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = 'SID';

on Linux/Unix, use:

kill -9 <spid>

On Windows, use:

taskkill /F /PID <spid>

Key Notes:

Avoid Killing Critical Sessions: Be careful when killing sessions, especially for production databases, as this can impact users or important processes.

Use with Caution: Always double-check the session details before terminating, to avoid accidentally stopping critical tasks.