First, you have to identify the session to be killed with alter system kill session.
Step-by-Step Instructions to Identify and Terminate a Session:
Invoke SQL*Plus:
First, open SQL*Plus to begin the process.
Query V$SESSION to Identify the Session:
Use the following query to retrieve the session details. This will list all active sessions with their respective SID, SERIAL#, STATUS, SCHEMANAME, and PROGRAM.
SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session;
The SID (Session Identifier) and SERIAL# values of the Oracle session to be killed can then be identified from this output.
Execute the ALTER SYSTEM Command:
Substitute the identified SID and SERIAL# values and issue the alter system kill session command.
ALTER SYSTEM KILL SESSION 'sid,serial#';
Handling Sessions Marked for Kill:
Sometimes, Oracle is not able to kill the session immediately with the alter system kill session command alone. Upon issuing the command, the session may be 'marked for kill' and will be terminated as soon as possible.
Forcing Session Termination:
In the case where a session is 'marked for kill' and not terminated immediately, you can force the termination by adding the immediate keyword:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Verify Termination:
To ensure that the session has been terminated, re-query the V$SESSION dynamic performance view:
SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';
The value of the STATUS column will be 'ACTIVE' when the session is making a SQL call and 'INACTIVE' if it is not.
Confirm PMON Cleanup:
After the Process Monitor (PMON) has cleaned up the session, the row will be removed from V$SESSION. Re-query to confirm:
SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';
Handling RAC Environments:
In a Real Application Clusters (RAC) environment, you can optionally specify the INST_ID, which is shown when querying the GV$SESSION view. This allows you to kill a session on a different RAC node.
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
By following these detailed steps, you can efficiently identify and terminate a session using SQL*Plus, ensuring minimal disruption and maintaining database integrity.
