How to purge a session thoroughly in Oracle

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 such an error as much as possible. It should also be noted that

If the session of Kill belongs to an Oracle background process, it can easily cause the database instance to be down.

Typically, there is no need to kill an Oracle session process from the operating system level, but this is not always the case, as described in the following description, which kills sessions at the Oracle level and kills the process at the operating system level.

First, get the information you need 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 sqlplus@oracle10g (TNS v1-v3) 4c621950 INACTIVE

1 160 27610 SYS sqlplus@oracle10g (TNS v1-v3) 4c624174 ACTIVE

1 144 27641 SCOTT sqlplus@oracle10g (TNS v1-v3) 4c624730 INACTIVE

Ii. using 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, it is necessary to understand which node the session where the Kill is to be located, and to query the Gv$session view for access.

The kill session is just killing the conversation. In some cases, a session that requires a Kill is not immediately killed because of a larger transaction or the need to run a longer SQL statement. For this Feeling

Condition will receive the "Marked for Kill" prompt (below), and 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

Sys@austin> alter system kill session ' 146,23 ';

System altered.

Sys@austin> alter system kill session ' 144,42 ';

System altered.

Sys@austin> Select Inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session where username are not Null

inst_id saddr SID serial# paddr USERNAME STATUS Program

---------- -------- ---------- ---------- -------- ---------- -------- ---------------------------------------------

1 4c70bf04 144 4c6545a0 SCOTT killed sqlplus@oracle10g (TNS v1-v3)

1 4c70e6b4 146 4c6545a0 TEST killed sqlplus@oracle10g (TNS v1-v3)

1 4c71fc84 160 4c624174 SYS ACTIVE sqlplus@oracle10g (TNS v1-v3)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/