Kill session in Oracle

Source: Internet
Author: User
Tags sessions

We know that in an Oracle database, a process can be terminated by a kill session with the basic syntax structure:

Alter system kill session 'sid,serial#';

When the session is killed, the status will be marked as killed,oracle the process will be cleared when the user next touch.


We find that when a session is killed, the paddr of the session is modified and if multiple sessions are killed, then multiple sessions
Paddr are changed to 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          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    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 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 case, most of the time, resources cannot be freed, and we need to query the SPID to kill these processes at the operating system level.

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

What else can I do?

Let's 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                        0SYS                              inactive 542b7bf8          1          15 24081                195 ev16 rows selected.

We note that the red mark is the process address of the process that was killed.

Simplifying a point is 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;

Ok, now that we have the process address, we can find the SPID in v$process, and then we can use kill or orakill to kill these processes at the system level.

In fact, I guess:

When you kill a session in Oracle, Oracle simply points the paddr of the associated session to the same virtual address.

At this point v$process and v$session lose relevance, and the process is interrupted.

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

If the process is killed at this time, re-attempt to perform the task, you will immediately receive a prompt for progress interrupt, and process exits, at which time Oracle will start Pmon
To clear the session. This is handled as an exception interrupt.


-the end-


Kill session in Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.