The kill session still exists and cannot be released immediately

Http://www.sosdb.com/jdul/dispbbs.asp? Boolean id = 2 & id = 248

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 inactive 542e5044 18 662 542b6d38 sys active SQL> alter system kill session '123 '; 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 killed 542e5044 18 662 542b6d38 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 11 314 542d6bd4 eygle killed 542e2aa4 14 397 542b7498 eqsp inactive 542e5044 18 662 542b6d38 sys active SQL> alter system kill session '123 '; 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 killed 542e2aa4 14 397 542d6bd4 eqsp killed 542e5044 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. ksl1_1r, 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 ksl1_1r 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 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 1 Username status ADDR ksllapsc ksllapsn ksllaspo ksl1_1r 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 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.