Research on Kill Session in Oracle
Author: Eygle
LINK:
http://www.eygle.com/faq/kill_session.htm
We know that in the Oracle database, we can terminate a process through the Kill Session, its basic grammar structure is:
Alter System Kill Session 'SID, Serial #';
The SESSION that is dropped by Kill is marked as killed, and Oracle clears the process when the user is next Touch.
We found that when a session is dropped by Kill, the session's Paddr is modified. If there are multiple session by kill, then multiple session's PADDR is changed to the same process address:
SQL> SELECT SADDR, SID, Serial #, Paddr, Username, Status from v $ sessions where username is not null; saddr sid serial # Paddr UserName Status -------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------------- ------ 542E0E6C 11 314 542B70E8 eYGLE INACTIVE542E5044 18 662 542B6D38 SYS ACTIVESQL> alter system kill session '11, 314 '; System altered.SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username Is Not Null; Saddr Sid Serial # Paddr UserName Status -------------------------------- --- ---------------------------------- 542E0E6C 11 314 542D6BD4 Eygle Killed542E5044 18 662 542B6D38 SYS ACTIVESQL> SELECT SADDR, SID, Serial #, Paddr, Username, Status from V $ Session Where Username Is Not Null; Saddr Sid Serial # Paddr U SERNAME STATUS ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ -------- 542E0E6C 11 314 542D6BD4 EYGLE KILED542E2AA4 14 397 542B7498 EQSP inactive542e5044 18 662 542B6D38 SYS ActiveSQL> ALTER SYSTEM KILL session '14, 397 ' System altered.sql> Select Saddr, SID, Serial #, Paddr, Username, Status from v $ session where username is not null;
Saddr Sid Serial # Paddr UserName Status ----------------------------------------- ------------------------------ 542E0E6C 11 314 542D6BD4 Eygle Killed542E2AA4 14 397 542D6BD4 EQSP Killed542E5044 18 662 542B6D38 SYS ACTIVE in this In the case, many times, the resources are unsealed, we need to query the SPID, to kill these processes in the operating system level.
But because the V $ session.paddr has changed, we cannot get the SPID via V $ Session and V $ Process associations.
What can I do?
Let's take a look at the following query:
SQL> Select S.username, S.Status, 2 x.addr, x.ksllapsc, x.ksllapsn, x.ksllaspo, x.ksllid1r, x.ksllrtyp, 3 decode (Bitand (x.ksuprflg, 2), 0, NULL, 1) 4 from x $ ksupr x, v $ session s 5 where s.paddr ( ) = x.addr 6 and bitand (ksspaflg, 1)! = 0;
UserName Status Addr Ksllapsc Ksllapsn Ksllaspo Ksllid1r Ks D ------------------------------------ --- ---------------------- ---------- - - 542B44A8 0 0 0 ACTIVE 542B4858 1 14 24069 0 1 ACTIVE 542B4C08 26 16 15901 0 1 ACTIVE 542B4FB8 7 46 24083 0 1 ACTIVE 542B5368 12 15 24081 0 1 ACTIVE 542B5718 15 46 24083 0 1 ACTIVE 542B5AC8 79 4 15923 0 1 ACTIVE 542B5E78 50 16 24085 0 1 Active 542B6228 754 15 24081 0 1 Active 542B65D8 1 14 24069 0 1 Active 542B6988 2 30 14571 0 1Username Status Addr Ksllapsc Ksllapsn Ksllaspo Ksllid1R KS D --------------------- ----------------------------------- ------- ---------- - - ---sys Active 542B6D38 2 8 24071 0 542B70E8 1 15 24081 195 EV 542B7498 1 15 24081
195 Evsys inactive 542b7848 0 0 0xys inactive 542b7bf8 1 15 24081 195 Ev16 rows selected. We note that the red word marked is the process address of the process that is dropped by Kill.
Simplify a little, actually the following concept:
SQL> SELECT P.Addr from V $ Process P where pid <> 1 2 minus 3 Select S.paddr from v $ session s;
Addr -------- 542B70E8542B7498
OK, now we get the process address, you can find SPID in V $ Process, and then you can use Kill or ORAKILL to kill these processes in system levels.
In fact, I guess:
When Kill Session in Oracle, Oracle simply points to the same virtual address of the associated session.
At this time, V $ Process and V $ SESSION lose associations, the process is interrupted.
Then Oracle waits for PMON to clear these session. So it is usually waiting for a SESSION exit that is marked as killed to take a long time.
If you are re-tried by Kill, you will receive a prompt from the process interrupt, and the process exits. At this time, Oracle will immediately start PMON to clear the session. This is an exception interrupt process.
Friday, June 25, 2004
If You Have Any Question, please mail to eye@itpub.net.
