首页 » ORACLE 9i-23ai » How to release still “killed“ status session in v$session?(释放killed的session)
How to release still “killed“ status session in v$session?(释放killed的session)
最近在一套生产库上发现了几个已经killed的会话一直保持在v$session 会话中，会话是user type的连接，而且已经killed了很久，通过SPID 发现操作系统层面已不存在该进程， 下面是我多次尝试后最终释放，这里简单的记录一下。
SQL> SELECT ses.username,
ses.sid,
ses.event,
ses.machine,
ses.program,
ses.status,
ses.last_call_et,
sql.hash_value,
ses.sql_id,
wait_time || ':' || SECONDS_IN_WAIT wai_secinwait,
blocking_instance || ':' || blocking_session bs,
-- SUBSTR (sql.sql_text, 1, 30) sqltext,
sql_child_number ch#
FROM v$session ses
LEFT JOIN
v$sql sql
ON ses.sql_hash_value = sql.hash_value
AND ses.sql_child_number = sql.child_number
WHERE ses.TYPE = 'USER' AND ses.status <> 'INACTIVE' -- and sql_text like 'select t.subsid,s.servnumber,t%'
ORDER BY SECONDS_IN_WAIT, last_call_et, 4;
USERNAME SID EVENT MACHINE PROGRAM STATUS LAST_CALL_ET HASH_VALUE SQL_ID WAI_SECINW BS CH#
---------- ---------- -------------------- ---------- -------------- -------- ------------ ---------- -------------------- ---------- ---------- ----------
...
ANBOB 8362 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 1589 2ad1tj9ph0799 0:238 1:6083
ANBOB 5675 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 4233911 2ad1tj9ph0799 0:271 1:6083
ANBOB 11136 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 2752356 2ad1tj9ph0799 0:272 1:6083
ANBOB 5699 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 4227707 2ad1tj9ph0799 0:272 1:6083
ANBOB 10373 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 2752406 2ad1tj9ph0799 0:278 1:6083
ANBOB 1559 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 4233819 2ad1tj9ph0799 0:278 1:6083
ANBOB 1001 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 4233977 2ad1tj9ph0799 0:278 1:6083
ANBOB 766 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 4234014 2ad1tj9ph0799 0:278 1:6083
ANBOB 6276 cursor: pin S wait o qdyyd1 oracle@qdyyd1 ACTIVE 2408703 2ad1tj9ph0799 0:279 1:6083
TBCS 11709 cursor: pin S wait o qdyya1 oracle@qdyya1 ACTIVE 3608489 2ad1tj9ph0799 0:279 1:6083
ANBOB 6083 SQL*Net message from qdyyd1 oracle@qdyyd1 ACTIVE 4227719 2ad1tj9ph0799 0:1589 : 1
ANBOB 2091 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 2408703 6wd9zqzvd1mhv 0:2408684 : 0
ANBOB 8857 SQL*Net message from HBYD\WIN-L plsqldev.exe ACTIVE 2752356 25zw3ur494zrk 0:2752335 : 0
ANBOB 4727 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 2752406 5um4j8rzxb7m4 0:2752385 : 0
ANBOB 4772 SQL*Net message from HBYD\WIN-L plsqldev.exe ACTIVE 4227707 g4kg17x0kdp2a 0:4227676 : 0
ANBOB 5092 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 4227719 5q4pm9fvgk1v2 0:4227688 : 0
ANBOB 6212 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 4233821 gnswv87thknxh 0:4233789 : 0
ANBOB 9651 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 4233911 3vsj9d74szj76 0:4233880 : 0
ANBOB 797 SQL*Net message from HBYD\WIN-L plsqldev.exe KILLED 4233977 5q4pm9fvgk1v2 0:4233946 : 0
ANBOB 4561 SQL*Net message from qdyyd2 oracle@qdyyd2 KILLED 4234015 2ad1tj9ph0799 0:4233984 : 0
SQL> select * from v$sql where sql_id='2ad1tj9ph0799';
no rows selected
SQL> @usid 4561
USERNAME SID AUDSID OSUSER MACHINE PROGRAM SPID OPID CPID SQL_ID HASH_VALUE LASTCALL STATUS SADDR PADDR TADDR LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ -------------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
DBMT '4561,54839' 76175883 grid qdyyd2 (TNS V1-V3) 28443 664 26551 2ad1tj9ph0799 1795169577 4234026 KILLED C0000015C2F43C00 C0000015B2369670 C00000159933E970 20150629 14:46:56
SQL> ho ps -ef|grep 28443
oracle 29899 27782 1 14:54:16 pts/3 0:00 grep 28443
Note:
OS 级进程已经不存在，但是v$session 视图中一直存在，该会话已经kill好几天，手动有尝试oradebug wakeup pmon进程，也是无法清理。而且sql_id为2ad1tj9ph0799但是没有sql hash_value,而且也没有在v$sql中查到，
SQL> @usid 4727
USERNAME SID AUDSID OSUSER MACHINE PROGRAM SPID OPID CPID SQL_ID HASH_VALUE LASTCALL STATUS SADDR PADDR TADDR LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ -------------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
DBMT '4727,4920' 94210564 59497A78D31F6009 \WIN-LTQ5KT3M57E plsqldev.exe 18504 1625 52780:39456 5um4j8rzxb7m4 4292189796 2759538 KILLED C0000015C2F9D2A0 C0000016023DC588 C000001572A4F488 20150716 18:20:25
SQL> host ps -ef|grep 18504
oracle 2428 3625 2 16:53:01 pts/2 0:00 grep 18504
SQL> alter system kill session '4727,4920' immediate;
alter system kill session '4727,4920' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
SQL> alter system disconnect session '4727,4920' immediate;
alter system disconnect session '4727,4920' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
SQL> oradebug setorapid 1625 force
Oracle pid: 1625, Unix process pid: 18504, image: oracle@qdyyd1
SQL> oradebug short_stack
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active
SQL> oradebug close_trace
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active
SQL> oradebug event immediate crash;
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active
SQL> select saddr from v$session where sid=4727;
SADDR
----------------
C0000015C2F9D2A0
SQL> select * from v$transaction where ses_addr='C0000015C2F9D2A0'
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPA REC NOU PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- --- ----------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
C000001572A4F488 1193 24 15385 0 0 0 0 ACTIVE 07/16/15 18:20:25 2681908016 3366 7 561 167090 2993 47 C0000015C2F9D2A0 4331011 NO NO NO NO 0 0 0 0 0 0 0 0 1 1 3 0 0 0 20150716 18:20:25 0 0 1.4460E+13 0 04A9001800003C19 0000000000000000 0000000000000000
SQL> select * from v$lock where sid=4727;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C000001572A4F488 C000001572A4F500 4727 TX 78184472 15385 6 0 2818353 2
SQL> select * from v$lock where id1=78184472 and id2=15385;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C000001572A4F488 C000001572A4F500 4727 TX 78184472 15385 6 0 2822991 2
SQL> @sw 4727
SID STATE EVENT SEQ# SEC_IN_WAIT P1 P2 P3 P1TRANSL
------- ------- ------------------------------ ---------- ----------- ------------------- ------------------- ------------------- -----------
4727 WAITING SQL*Net message from dblink 52 2819299 driver id= #bytes= 1 0
0x0000000054435000
Note:
可以看到会话在等待dblink响应，而且一直在执行tx lock 未释放，用oradebug的手段也没有结束会话， 而且上面我们看到在会话执行SQL_ID 2ad1tj9ph0799也会也是wait在解析阶段， 因为无法确认SQL文件，无法从数据库对象下手，不过他们有一个共性就是使用的都是ANBOB这样的我们定义管理数据库用户，数据库应用没有使用该用户，想通过lock 该用户，重新解锁的方式尝试释放该会话。
alter user anbob account lock; alter user anbob account unlock;
再次查询发现僵了好几天的killed 状态的会话终于释放，且未在出现。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。