Kill session in Oracle

We know that in the Oracle database, a process can be terminated by kill session. The basic syntax structure is as follows:

Alter system kill session'Sid, serial #';

 

The kill session will be marked as killed, and Oracle will clear the process during the next touch of the user.

We found that when a session is killed, the paddr of the session is modified. If multiple sessions are killed
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, resources cannot be released. We need to query spids and kill these processes at the operating system level.

However, because V $ session. paddr has changed, we cannot associate v $ session with V $ process to obtain the spid.

What else can we do?

Let's take a look at the following query in detail:

 

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 text marks the process address of the killed process.

To simplify the process, the following concepts are used:

SQL> select p.addr from v$process p where pid <> 1 2 minus 3 select s.paddr from v$session s; ADDR -------- 542b70e8 542b7498

OK. Now we get the process address, and we can find the spid in V $ process. Then we can use kill or orakill to kill these processes at the system level.

Actually, I guess:

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

At this time, the V $ process and the V $ session are lost, and the process is interrupted.

Oracle then waits for pmon to clear these sessions. Therefore, it usually takes a long time to wait for a session marked as killed to exit.

If the kill process re-executes the task, the system immediately receives a message indicating that the process is interrupted. At this time, Oracle immediately starts pmon.
To clear the session. This is treated as an exception interrupt.

-The end-