Kill Oracle Sessions
(a) SQL* PLUS:
In order to use SQL*PLUS Approach, first we need to find out the SID, SERIAL# of the Session which we want to kill. Then issue the following command to kill the session
SQL> alter system kill session ‘SID, SERIAL#’;
When we issue the above statement, It terminates a session, rolls back ongoing transactions, releases all session locks, frees all session resources. If the session is performing some activity that must be completed (e.g. waiting for a reply from a remote database or rolling back a transaction), Oracle waits for this activity to complete, kills the session then returns control. If the wait lasts for 60 seconds then Oracle marks the session to be killed, and returns control with a message that the session is marked to be killed. It then gets killed when the activity is complete.
(b) Using KILL Command (Unix/Linux)
To kill the sessions using KILL Command, we need to find out the SPID ( Server Process ID) of the Oracle Session. Then issue the KILL Command
$ kill -9 <SPID>
(c) Using ORAKILL Command ( Windows)
To kill the sessions using ORAKILL Command ( Windows), we need to find out the SPID of Session and ORACLE_SID of your Oracle Database. Then issue ORAKILL Command
C:\> orakill <ORACLE_SID> <SPID>
Here find the practical approach
Using SQL*PLUS
SQL> select username,sid,serial#,terminal from v$session;
USERNAME SID SERIAL# TERMINAL
—————————— ———- ————-
SCOTT 134 47747 BSR
SYS 147 46965 pts/0
150 2 UNKNOWN
151 4 UNKNOWN
154 1 UNKNOWN
167 1 UNKNOWN
168 1 UNKNOWN
169 1 UNKNOWN
170 1 UNKNOWN
18 rows selected.
SQL> alter system kill session ’134,47747′ immediate;
System altered.
================================================================
Using Unix KILL Command:
==============================
SQL> SELECT s.sid, p.spid, s.osuser, s.programFROM v$process p, v$session sWHERE p.addr = s.paddr;
SID SPID OSUSER PROGRAM
———- ———— ——————————
170 15771 oracle oracle@oraprod (PMON)
169 15773 oracle oracle@oraprod (PSP0)
168 15775 oracle oracle@oraprod (MMAN)
167 15777 oracle oracle@oraprod (DBW0)
166 15779 oracle oracle@oraprod (LGWR)
165 15781 oracle oracle@oraprod (CKPT)
164 15783 oracle oracle@oraprod (SMON)
163 15785 oracle oracle@oraprod (RECO)
162 15787 oracle oracle@oraprod (CJQ0)
161 15789 oracle oracle@oraprod (MMON)
160 15791 oracle oracle@oraprod (MMNL)
138 16425 oracle oracle@oraprod (J000)
154 15799 oracle oracle@oraprod (QMNC)
147 16259 oracle sqlplus@oraprod (TNS V1-V3)
150 15807 oracle oracle@oraprod (q000)
151 15809 oracle oracle@oraprod (q001)
137 16329 oracle sqlplus@oraprod (TNS V1-V3)
134 16380 BSR sqlplus.exe
18 rows selected.
$ ps -ef|grep 16380
oracle 16436 16236 0 17:30:37 pts/0 0:00 grep 16380
oracle 16380 1 0 17:28:32 ? 0:00 oracleORCL (LOCAL=NO)
$ kill -9 16380$
======================================================================
Using Windows ORAKILL Command:
========================================
SQL> select sid,username,program from v$session;
SID USERNAME PROGRAM
———- ——————————
1 ORACLE.EXE
2 ORACLE.EXE
3 ORACLE.EXE
4 ORACLE.EXE
5 ORACLE.EXE
6 ORACLE.EXE
7 ORACLE.EXE
8 ORACLE.EXE
9 SCOTT sqlplus.exe
10 SYS sqlplus.exe
Find out the SPID related to SCOTT Session (Session ID 9):
SQL> select s.sid,p.spid,s.osuser,s.program from v$session s, v$process p where p.addr=s.paddr;
SID SPID OSUSER PROGRAM
———- ———— ——————————
1 1744 SYSTEM ORACLE.EXE
2 3716 SYSTEM ORACLE.EXE
3 3644 SYSTEM ORACLE.EXE
4 3888 SYSTEM ORACLE.EXE
5 1992 SYSTEM ORACLE.EXE
6 2092 SYSTEM ORACLE.EXE
7 2148 SYSTEM ORACLE.EXE
8 2224 SYSTEM ORACLE.EXE
9 2720 BSR sqlplus.exe
10 2144 BSR sqlplus.exe
10 rows selected.
Kill the SPID 2720 related to SCOTT SID 9 session using orakill command
C:\>orakill TEST 2720
Kill of thread id 2720 in instance TEST successfully signaled.
C:\> SQL> select sid,username,program from v$session
SID USERNAME PROGRAM
———- ——————————
1 ORACLE.EXE
2 ORACLE.EXE
3 ORACLE.EXE
4 ORACLE.EXE
5 ORACLE.EXE
6 ORACLE.EXE
7 ORACLE.EXE
8 ORACLE.EXE
10 SYS sqlplus.exe
9 rows selected
Note: Do not kill the sessions at the OS level as per Oracle Recommendation. As per understanding i have given the above approaches
Reference:
(1) Should Sessions be Killed in OS or Using Alter System Kill Session? Doc ID: Note:161794.1
No comments:
Post a Comment