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

Viewing Screenshots

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Overview

Back to List of Topics

In this module you will learn how to terminate sessions using SQL*Plus and Enterprise Manager.

You should always terminate user sessions using Oracle tools. However, if the operating system process related to a terminated Oracle user session remains active, you can kill the session-related operating system process by following the appropriate steps for your operating system:

	Terminating Session-related Operating System Processes on UNIX
	Terminating Session-related Operating System Processes on Windows

Terminating Sessions Using SQL*Plus

Back to List of Topics

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, SERVER FROM V$SESSION WHERE USERNAME = '<username>'
3.	Execute the ALTER SYSTEM command to terminate the session: ALTER SYSTEM KILL SESSION '<sid, serial#>'
4.	Query V$SESSION: SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME = '<username>'
5.	After PMON has cleaned up after the session, the row is removed from V$SESSION: SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME = '<username>'

Terminating Sessions Using Enterprise Manager

Back to List of Topics

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

Back to List of Topics

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 spid FROM v$process WHERE 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 = '<username>';
4.	At the operating system prompt, issue the kill command and supply the operating system process identifier (spid): kill <spid>

Terminating Session-related Operating System Processes on Windows

Back to List of Topics

Invoke SQL*Plus.

Issue the following query to determine the operating system process identifier (spid) or thread:

SELECT spid, s.osuser, s.program
FROM   v$process p, v$session s
WHERE  p.addr = s.paddr;

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 <sid> <thread>