The study of kill session in Oracle

Oracle|session
The study of kill session in Oracle



Author: eygle

Link

Http://www.eygle.com/faq/Kill_Session.htm

We know that in an Oracle database, you can terminate a process by means of a kill session, whose basic syntax structure is:

Alter system kill session ' sid,serial# ';



The session being killed, the status is marked as Killed,oracle clears the process the next time the user touch it.

We find that when a session is killed, the paddr of the sessions is modified, and if multiple sessions are killed, the paddr of multiple sessions are changed to the same process address:

Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null; saddr           sid    SERIAL# PADDR     username                        STATUS-------------------------------------------------------------------- ------542e0e6c         11        314 542b70e8 eygle                           inactive542e5044          18        662 542b6d38 sys                            &nbsP Activesql> alter system kill session ' 11,314 '; System altered. Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are 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 isn't null; saddr           sid    SERIAL# PADDR     username                        STATUS-------------------------------------------------------------------- ------542e0e6c         11        314 542d6bd4 eygle                           killed542e2aa4          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 are not null; saddr           sid    SERIAL# PADDR     username                        STATUS-------------------------------------------------------------------- ------542e0e6c         11        314 542d6bd4 EYGLE  &Nbsp;                        killed542e2aa4         14         397 542d6bd4 eqsp                            killed542e5044          18        662 542b6d38 SYS                              ACTIVE

In this case, many times, resources cannot be freed, and we need to query the SPID to kill these processes at the operating system level.

But since V$SESSION.PADDR has changed, we cannot get the SPID through v$session and v$process associations.

What can we do about it?

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   &NBsp;      0                        0                                 active   542b4858          1          24069                  0    1                                 active   542b4c08         26          15901                  0    1                                 active   542b4fb8          7          24083                  0    1                                 active   542b5368         12          24081                  0    1                                ACTIVE    542b5718         15          24083                 0     1                                active   542b5ac8         79           4 15923                 0     1                                active   542b5e78         50         24085                  0    1                                 active   542b6228         754         24081                  0    1                                 active   542b65d8           1         24069                  0    1                                 active   542b6988           2         14571                  0    1username                         status   addr       ksllapsc   KSLLAPSN KSLLASPO        ksllid1r KS D--------------------------------------------------------------- ----------------------------sys             & Nbsp;              ACTIVE   542B6D38           2          8 24071                 0                                           542b70e8          1          24081               195 ev                                          542b7498          1          24081               195 evsys                             INACTIVE 542b7848           0          0                        0SYS                              INACTIVE 542b7bf8           1         24081                195 EV16 rows selected.



We note that the marked part of the red Word is the process address of the process that was killed.

To simplify a little, it is the following concepts:

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 that we have the process address, we can find the SPID in the v$process, and then kill or orakill the process at the system level.

In fact, I guess:

After the kill session in Oracle, Oracle simply points the paddr of the relevant session to the same virtual address.

At this point v$process and V$session lost their association and the process was interrupted.

Oracle then waits for Pmon to clear the sessions. So it usually takes a long time to wait for a session to be marked as killed.

If you kill the process at this time, try to perform the task again, then immediately receive a prompt for the progress interruption, the procedure exits, at which time Oracle will immediately start Pmon to clear the session. This is handled as an exception interrupt.





Friday, June 25, 2004

If you are have any question,please mail to eygle@itpub.net.