• Article Type: General
• Product: Aleph

Desired Outcome Goal:
Kill an unwanted Oracle database session in a clean way.

Procedure:
(1)
Determine the process ID (PID) of the operating system process which corresponds to the database session.

Examples:

(1.1)
You suspect that a database session is spinning and consuming a significant amount of CPU time.
In this case the UNIX command top may report the corresponding operating system process as one of the top consumers of CPU time and provide its PID.


(1.2)
You know the parent process which initiated the database session you want to kill, e.g. a script that called SQL*Plus which in turn opened the database session.
In this case the parent process has an sqlplus process as its child, and that sqlplus process has a database server process as its child:

parent process:
aleph 8482 32481 0 16:35 pts/1 00:00:00 csh -f p_custom_02 USM50,loanst,09,

sqlplus process:
aleph 8605 8482 0 16:35 pts/1 00:00:00 sqlplus @/exlibris/aleph/u21_1/alephe/scripts/sql/patron.sql USM50 loanst 09

database server process:
oracle 8606 8605 99 16:35 ? 00:59:28 oraclealeph21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))



(2)
Connect to the Oracle database with privilege sysdba, e.g. by entering the following command as UNIX user oracle:

$ sqlplus / as sysdba


(3)
Determine the Session Identifier (SID) and the Session Serial Number (SERIAL#) of the database session which corresponds to the operating system PID you determined in Step 1:

Example: PID = 8606

SQL> SELECT s.SID, s.SERIAL#, p.SPID, s.PROGRAM
2 FROM V$PROCESS p, V$SESSION s
3 WHERE p.ADDR = s.PADDR
4* and p.SPID = '8606';

SID SERIAL# SPID PROGRAM
---------- ---------- ---------- ----------------------------------------
23 61603 8606 sqlplus@<server_name> (TNS V1-V3)


(4)
Kill the database session by executing the SQL statement ALTER SYSTEM KILL SESSION '<sid>,<serial#>' .

Example: SID = 23, SERIAL# = 61603

SQL> ALTER SYSTEM KILL SESSION '23,61603';

or:

SQL> ALTER SYSTEM KILL SESSION '23,61603' IMMEDIATE;

Category: Installation & Upgrades (500) - ALEPH

Subject: Installation - ALEPH

• Article last edited: 8/29/2014