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.
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:
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>'
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
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.program FROM v$process p, v$session s WHERE 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 <sid> <thread>
Move your mouse over this icon to hide all screenshots
