SESSION处于KILLED状态下如何找出对应的进程

最新推荐文章于 2021-04-11 07:40:19 发布

cuilin2183

最新推荐文章于 2021-04-11 07:40:19 发布

阅读量428

点赞数

今天在处理一个会话满了的数据库，需要释放部分无用的session。根据V$SESSION的program可以简单确定PL/SQLDEV连过去的会话基本是无效会话，需要把这些会话都kill掉：[@more@]

SQL> select 'alter system kill session '''||sid||','||SERIAL#||''';' from v$session where program like 'PlSqlDev.exe%';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------------------------------------
alter system kill session '12,14459';
alter system kill session '18,379';
alter system kill session '35,63717';
alter system kill session '43,37798';
alter system kill session '56,38333';
alter system kill session '106,59769';
alter system kill session '138,18817';
alter system kill session '163,64575';
alter system kill session '186,18270';
alter system kill session '219,43397';
alter system kill session '266,32098';
alter system kill session '267,64616';
alter system kill session '306,15258';
alter system kill session '309,21923';

--执行以上语句

--抽查其中一个会话的状态

SQL> select * from v$session where sid=12;

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ----------
OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME
---------- ---------------- ---------------- -------- --------- ---------- ------------------------------
OSUSER PROCESS MACHINE
------------------------------ ------------ ----------------------------------------------------------------
TERMINAL PROGRAM TYPE SQL_ADDRESS
------------------------------ ------------------------------------------------ ---------- ----------------
SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER
-------------- ------------- ---------------- ---------------- --------------- ------------- -----------------
PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID
--------------------- ------------------------- --------------- -------------------
MODULE MODULE_HASH ACTION ACTION_HASH
------------------------------------------------ ----------- -------------------------------- -----------
CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE#
---------------------------------------------------------------- -------------------- ------------- --------------
ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI
--------------- ------------- ------------ ------------ --- ------------- ---------- ---
RESOURCE_CONSUMER_GROUP PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
-------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION
---------------------------------------------------------------- ----------- ----------------- ----------------
SEQ# EVENT# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------- ----------------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- ----------------
P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS#
---------------------------------------------------------------- ---------- ---------------- ------------- -----------
WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- --------------- -------------------
SERVICE_NAME SQL_TRAC SQL_T SQL_T
---------------------------------------------------------------- -------- ----- -----
00000000FC523C28 12 14459 2217887 00000000FC50C798 40 ID5ZZ 0
2147483644 KILLED PSEUDO 40 ID5ZZ
liping 7380:6428 WORKGROUPGZT-120
GZT-120 PlSqlDev.exe USER 00
0 00000000D5331888 2674323898 bydf32qgqdwdu 1

PL/SQL Developer 1190136663 SQL Window - select * from query 3567217222
11937839 14097 6
380 0 16-APR-08 20262 NO NONE NONE NO
DISABLED ENABLED ENABLED 0
NO HOLDER
106 257 SQL*Net message from client
driver id 1413697536 0000000054435000
#bytes 1 0000000000000001
0 00 2723168908 6
Idle 0 20262 WAITING
SYS$USERS DISABLED FALSE FALSE

可以看到会话的状态为killed，等了十分钟，会话的状态仍然没有改变，该会话仍然在V$SESSION中，也就是仍然占用的连接。此时可以考虑直接在操作系统上直接kill掉对应的进程。

但是由于ORACLE的bug，此时是没有办法直接关联V$SESSION和V$PROCESS得到会话对应的SPID的：

Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS

此时的解决办法是：

SQL> select spid, program from v$process
2 where program!= 'PSEUDO'
3 and addr not in (select paddr from v$session)
4 and addr not in (select paddr from v$bgprocess);

SPID PROGRAM
------------ ------------------------------------------------
18180 oracletest@myserver
18206 oracletest@myserver
15851 oracletest@myserver
15321 oracletest@myserver
15327 oracletest@myserver
19766 oracletest@myserver
15095 oracletest@myserver
15101 oracletest@myserver
15109 oracletest@myserver
15267 oracletest@myserver
32742 oracletest@myserver
15477 oracletest@myserver
22059 oracletest@myserver
22550 oracletest@myserver