Oracle Kill session

The kill session is one of the things that DBAs often encounter. If you kill a session that should not kill, it is destructive, so avoid this error as much as possible. It should also be noted that
If the session of Kill is part of an Oracle background process, it is easy to cause the DB instance to crash.
Normally, it is not necessary to kill the Oracle session process from the operating system level, but this is not always the case, given in the following description the kill session at Oracle level and the OS level kill process.
First, get the information that needs to kill SESSION (using V$session and gv$session views)
SET Linesize 180
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A40
SELECT s.inst_id,
S.sid,
s.serial#,
P.spid,
S.username,
S.program,
S.PADDR,
S.status
From Gv$session S
JOIN gv$process p on p.addr = s.paddr and p.inst_id = s.inst_id
WHERE s.type! = ' BACKGROUND ';
inst_id SID serial# SPID USERNAME program Paddr STATUS
---------- ---------- ---------- ---------- ---------- --------------------------------------------- -------- --------
1 146 27573 TEST [email protected] (TNS v1-v3) 4c621950 INACTIVE
1 27610 SYS [email protected] (TNS v1-v3) 4c624174 ACTIVE
1 144 27641 SCOTT [email protected] (TNS v1-v3) 4c624730 INACTIVE
Second, use the Alter SYSTEM KILL SESSION command to implement
Grammar:
sql> ALTER SYSTEM KILL SESSION ' sid,serial# ';
sql> ALTER SYSTEM KILL SESSION ' sid,serial# ' IMMEDIATE;
For the kill session in a RAC environment, you need to figure out which node the session in which you want to kill is located, and you can query the Gv$session view for it.
The kill session is simply killing the conversation. In some cases, a session that requires kill cannot be killed immediately because of a larger transaction or the need to run a longer SQL statement. For this kind of feeling
will receive the "Marked for Kill" prompt (below), the session is immediately killed once the session's current transaction or operation completes.
Alter system kill session ' 4730,39171 '
*
ERROR at line 1:
Ora-00031:session marked for Kill
Session 146,144 will be killed in the following operation
[Email protected]> alter system kill session ' 146,23 ';
System altered.
[Email protected]> alter system kill session ' 144,42 ';
System altered.
[Email protected]> Select Inst_id,saddr,sid,serial#,paddr,username,status,program from Gv$session whereusername are Not null;
inst_id saddr SID serial# paddr USERNAME STATUS Program
---------- -------- ---------- ---------- -------- ---------- -------- ---------------------------------------------
1 4c70bf04 144 4c6545a0 SCOTT killed [email protected] (TNS v1-v3)
1 4c70e6b4 146 4c6545a0 TEST killed [email protected] (TNS v1-v3)
1 4c71fc84 4c624174 SYS ACTIVE [email protected] (TNS v1-v3)
Note: In the query you can see that the paddr address of the killed session has changed, referencing the red font in the query results. If multiple sessions are killed, multiple session PADDR
be changed to the same process address.
Use the following statement to retrieve the previous address of the addr that was killed.
SELECT S.username,s.status,
X.addr,x.ksllapsc,x.ksllapsn,x.ksllaspo,x.ksllid1r,x.ksllrtyp,
Decode (Bitand (x.ksuprflg,2), 0,null,1)
From X$KSUPR x,v$session s
WHERE s.paddr (+) =x.addr
and Bitand (ksspaflg,1)!=0;
USERNAME STATUS ADDR ksllapsc ksllapsn ksllaspo ksllid1r KS D
---------- -------- -------- ---------- ---------- ------------ ---------- -- -
ACTIVE 4C623BB8 4 27468 275 EV 1
ACTIVE 4c623040 9 24 27444 0 1
ACTIVE 4c622a84 101 4 27480 274 EV 1
ACTIVE 4c6224c8 1 48 27450 0 1
ACTIVE 4c621f0c 1 48 27450 0 1
ACTIVE 4C6235FC 2 4 27468 0 1
SYS ACTIVE 4c624174 2 15 27442 0
ACTIVE 4c62081c 1 48 27440 0 1
ACTIVE 4c621394 1 48 27440 0 1
ACTIVE 4c620dd8 11 24 27476 0 1
ACTIVE 4c61f6e8 15 4 27610 0 1
ACTIVE 4c620260 222 24 27450 0 1
ACTIVE 4C61FCA4 7 25 27573 0 1
ACTIVE 4c61f12c 6 25 27573 0 1
ACTIVE 4c61eb70 4 24 27458 0 1
ACTIVE 4c61e5b4 1 48 27440 0 1
ACTIVE 4c61dff8 2 24 27444 0 1
4c624730 0 0 0
4c621950 0 0 0
4C61DA3C 0 0 0
or according to the following statement to obtain the change of the addr
[Email protected]> Select p.addr from V$process p where PID <> 1
2 minus
3 Select s.paddr from V$session s;
ADDR
--------
4c621950
4c624730
Third, kill the session at the operating system level
Find the process ID of the operating system for the session
[Email protected]> Select SPID from v$process where ADDR in (' 4c621950 ', ' 4c624730 ');
SPID
----------
27573
27641
Use the KILL command to kill the operating system level process ID
Kill Session-9 27573
Kill Session-9 27641
Iv. obtaining the SID for the current session
Sql> Select Userenv (' Sid ') from dual;
USERENV (' SID ')
--------------
627
Five or more sessions require kill handling
1. Find information about the session that needs to be killed based on the given SID (user name), including which instance is located
Set Linesize 160
Col Program format A35
Col username format A18
Select Inst_id,saddr,sid,serial#,paddr,username,status,program from Gv$session
where Sid in (' 2731 ', ' 2734 ', ' 2720 ', ' 2678 ', ' 2685 ')
and Username= ' Cticust '
Order BY inst_id;

inst_id saddr SID serial# paddr USERNAME STATUS Program
---------- ---------------- ---------- ---------- ---------------- ------------------ -------- ----------------------- ----
1 00000003daf8f870 2678 8265 00000003dbc6ca08 mss4usr INACTIVE JDBC Thin Client
1 00000003daf98e48 2685 00000003dbc08510 mss4usr ACTIVE JDBC Thin Client
1 00000003dafc7b80 2720 5 00000003dbbeda20 mss4usr INACTIVE JDBC Thin Client
1 00000003dafd66f8 2731 3 00000003dbbe9ae0 SYS ACTIVE [email protected] (TNS v1-v3)
1 00000003dafda730 2734 00000003dbbec268 mss4usr INACTIVE JDBC Thin Client
2 00000003dafd66f8 2731 1 00000003dbbe92f8 ACTIVE [email protected] (ARC0)
In the above query, there is a SID of 2731 on Node 2.
You can also obtain the node information of the RAC in the following way to determine which node the session needs to kill on.
Set Linesize 160
Col host_name format A25
Sql> Select Instance_number,instance_name,host_name,version,status from Gv$instance order by 1;
Instance_number instance_name host_name VERSION STATUS
--------------- ---------------- ------------------------- ----------------- ------------
1 o02wmt1a svd0051 10.2.0.4.0 OPEN
2 o02wmt1b svd0052 10.2.0.4.0 OPEN
3 o02wmt1c svd0053 10.2.0.4.0 OPEN
2. Use the following query to generate a Kill session statement
Select ' Alter system kill session ' | | Sid | | ', ' | | serial# | | "' | | '; ' from gv$session
where Sid in (' 2731 ', ' 2734 ', ' 2720 ', ' 2678 ', ' 2685 ')
Order BY inst_id;
Get the following kill session statement, according to the request because this need to kill the session is all located in Node 1, so log on to node node 1 executes the following statement
Alter system kill session ' 2678,8265 ';
Alter system kill session ' 2685,83 ';
Alter system kill session ' 2720,5 ';
Alter system kill session ' 2731,3 ';
Alter system kill session ' 2734,15 ';

Oracle Kill session