ORACLE - Oracle Kill session in the studyORACLE | Programing Reference For .Net,Java,C

Oracle Kill session in the study

Author: Eygle

Link:

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

We know that in the Oracle database, you can kill session way to terminate a process, and its basic grammatical structure:

Alter system kill session 'sid, serial #';

Kill by the fall session, the state will be marked as killed, Oracle users will be in the next touch, remove the process.

We found that when a session was kill off after the session of paddr be changed, if there are multiple session was kill, then the number of session
Paddr the changes have been for the same process Address:

SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;

SADDR SID SERIAL # PADDR USERNAME STATUS
——– ———- ———- ——– ————– —————- ——–
542E0E6C 11314542 B70E8 EYGLE INACTIVE
542E5044 18662542 B6D38 SYS ACTIVE

SQL> 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 11314542 D6BD4 EYGLE KILLED
542E5044 18662542 B6D38 SYS ACTIVE

SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;

SADDR SID SERIAL # PADDR USERNAME STATUS
——– ———- ———- ——– ————– —————- ——–
542E0E6C 11314542 D6BD4 EYGLE KILLED
542E2AA4 14397542 B7498 EQSP INACTIVE
542E5044 18662542 B6D38 SYS ACTIVE

SQL> 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 11314542 D6BD4 EYGLE KILLED
542E2AA4 14397542 D6BD4 EQSP KILLED
542E5044 18662542 B6D38 SYS ACTIVE

In these circumstances, very often, the resources are not released, we need enquiries spid, the operating system level to kill these processes.

However, because at this time v $ session.paddr has changed, we can not adopted v v $ session and association process to obtain $ spid

How can it be done?

We look at the following enquiries:

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 2408 1 0 1
ACTIVE 542B65D8 1 14 24069 0 1
ACTIVE 542B6988 2 30 14571 0 1

USERNAME 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 EV
SYS INACTIVE 542B7848 0 0 0
SYS INACTIVE 542B7BF8 1 15 24081 195 EV

16 rows selected.

We note that the part of The Scarlet Letter is marked by the process of Kill off the process of address.

Simplify, the concept is actually as follows:

SQL> select p.addr from v $ process p where pid <> 1
Minus 2
3 select s.paddr from v $ session s;
ADDR
——–
542B70E8
542B7498

Ok, now we have a process address, we can find v $ spid process, and then can be used orakill Kill or at the system level to kill these processes.

Indeed, I would hazard a guess:

When Oracle kill session in the future, Oracle simply related to the paddr session at the same virtual address.

$ Process at this time v v $ session and lost relevance, interrupted this process.

Oracle then on the waiting PMON to remove these Session. Therefore usually wait for a marker to be out Killed Session of the need to spend a very long time.

If this was Kill the process, the implementation of tasks to try, then immediately receive tips interruptions in the process, process out, then Oracle will immediately implement the PMON
To remove the session. This was an anomaly as interrupt handling.

June 25, 2004 Friday

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

Bookmark it: These icons link to social bookmarking sites where readers can share and discover new web pages.