Terminating Oracle Sessions

Terminating Sessions and Cleaning Up Processes:
Purpose
This module describes how you can terminate sessions and clean up session-related operating system processes.

Topics:
This module discusses the following:
Overview
Terminating Sessions Using SQL*Plus
Terminating Sessions Using Enterprise Manager
Terminating Session-related Operating System Processes on UNIX
Terminating Session-related Operating System Processes on Windows

Terminating Sessions Using SQL*Plus:
You can terminate sessions with the ALTER SYSTEM KILL command. When you issue the ALTER SYSTEM KILL session command, you must specify the session's index number and serial number. To identify the session index number (sid) and serial number of a session, query the V$SESSION dynamic performance view as shown below. The value of the STATUS column will be ACTIVE when the session is making a SQL call to Oracle. It will be INACTIVE if it is not making a SQL call to Oracle.

Identify the correct session and terminate the session by performing the steps below:

1.Invoke SQL*Plus.

2.Query V$SESSION supplying the username for the session you want to terminate:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

3.Execute the ALTER SYSTEM command to terminate the session:

ALTER SYSTEM KILL SESSION ''

4.Query V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

5.After PMON has cleaned up after the session, the row is removed from V$SESSION:

SELECT SID, SERIAL#, STATUS, SERVERFROM V$SESSIONWHERE USERNAME = ''

Terminating Sessions Using Enterprise Manager:
Identify the correct session and terminate the session by performing the steps below.
Note: Oracle Enterprise Manager 9.2 was used in the examples.
1.Select START > Programs > Oracle > OraHome92 > Enterprise Manager Console. Select Launch Standalone and click OK.

2.Expand Databases. Expand your database. Expand Instance and select Sessions. Identify the session you want to terminate.

3.Expand Sessions. Select the session you want to terminate and click Kill Session:

4.Select Immediate and click OK:

5.STATUS changes to KILLED and SERVER changes to PSEUDO:

6.The row is removed after PMON has cleaned up after the session.

Terminating Session-related Operating System Processes on UNIX:
The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.Invoke SQL*Plus.

2.Issue the following query to determine the operating system process identifier (spid):
SELECT spidFROM v$processWHERE NOT EXISTS (SELECT 1
FROM v$session WHERE paddr = addr);

3.If you are unable to identify the operating system process identifier (spid) from the query in step 2, you can issue the following query to help identify the correct session:
SELECT s.sid, s.serial#, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = '';

4.At the operating system prompt, issue the kill command and supply the operating system process identifier (spid):
kill

Terminating Session-related Operating System Processes on Windows:
The PMON background process will clean up after any user session you terminate with the ALTER SYSTEM KILL SESSION command. You can kill the session-related operating system process by performing the steps outlined below:

1.Invoke SQL*Plus.

2.Issue the following query to determine the operating system process identifier (spid) or thread:
SELECT spid, s.osuser, s.programFROM v$process p, v$session sWHERE p.addr = s.paddr;

3.At the operating system prompt, issue the orakill command. Supply the SID and the thread which you obtained from the SPID column in step 2:
orakill