ITPub博客

首页 > 数据库 > Oracle > SESSION处于KILLED状态下如何找出对应的进程

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

原创 Oracle 作者:space6212 时间:2019-03-28 20:12:06 0 删除 编辑
今天在处理一个会话满了的数据库,需要释放部分无用的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

以上的查询出来的SPID就是之前我们删除的会话对应的SPID。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-1002729/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    166128