We know that in the Oracle database, we can terminate a process through the Kill Session, its basic grammar structure is:
Alter System Kill Session 'SID, Serial #';
The SESSION that is dropped by Kill is marked as killed, and Oracle clears the process when the user is next Touch.
We found that when a session is dropped by Kill, the session's Paddr is modified. If there are multiple session by kill, then multiple session's PADDR is 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 '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 KILED
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 KILED
542E2AA4 14 397 542B7498 EQSP inactive
542E5044 18 662 542B6D38 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 11 314 542D6BD4 EYGLE KILED
542E2AA4 14 397 542D6BD4 EQSP Killed
542E5044 18 662 542B6D38 SYS ACTIVE
In this case, many times, the resource cannot be released, we need to query the SPID, to Kill in the operating system level.
But because the V $ session.paddr has changed, we cannot get the SPID via V $ Session and V $ Process associations.
What can I do?
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 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 1Active 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 red word marks is the process address of the process that is dropped by Kill.
Simplify a little, 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;
Addr -------- 542B70E8542B7498
OK, now we get the process address, you can find SPID in V $ Process, and then you can use Kill or ORAKILL to kill these processes in system levels.
In fact, I guess:
When Kill Session in Oracle, Oracle simply points to the same virtual address of the associated session.
At this time, V $ Process and V $ SESSION lose associations, the process is interrupted.
Then Oracle waits for PMON to clear these session. So it is usually waiting for a SESSION exit that is marked as killed to take a long time.
If you are re-tried by Kill, you will receive a prompt from the process interrupt, and the process exits. At this time, Oracle will immediately start PMON to clear the session. This is an exception interrupt process.
----------------------------
About this article, I am working with me:
1. Use your own script conn to view the SID that is currently blocked or blocked, # serial
2. Use alter system kill session 'sID, # serial';
3. The previous step may have an error, it doesn't matter, just a killed flag.
4. Use the following statement to check, hit the killed flag session.select saddr, sid, serial #, paddr, username, status from v $ session where username is not null order by 6 /
5. Log in with SQLPlus Sys / Change_on_install @ orcl.
carried out:
Select S.Username, S.Status, x.addr, x.ksllapsc, x.ksllapsn, x.ksllaspo, x.ksllid1r, x.ksllrtyp, decode (Bitand (x.ksuprflg, 2), 0, NULL, 1) From x $ ksupr x, v $ session swhere s.paddr ( ) = x.addrand Bitand (Ksspaflg, 1)! = 0ORDER BY 2 /
6. Isolated on all Killed flags, or status for empty addr.
7.
SELECT SPID, TERMINAL, Program, UserName from V $ Process Where Addr = '00000004008DDFF8' or Addr = '..........
8. Isors all SPID values.
9. Under UNIX Database Server, log in with root, PS-EF | GREP SPID, confirm the process you need to kill.
10. If it is a general connection process, which is or the like, all killed with Kill -9 SID.
11. Blocking problems.
