kill某个等待事件对应的spid：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.background is null
   and b.type = 'USER'
   and b.event like '%' || '&eventname' || '%'
   and b.status = 'ACTIVE';

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

对应的alter system kill session的语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
set linesize 260 pagesize 1000
col machine for a50
col kill_session for a60;
select machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
       status
  from v$session
 where type='USER' and event like '%event_name%' and status = 'ACTIVE';

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

kill某个sql_id对应的spid：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.background is null
   and b.type = 'USER'
   and b.sql_id = '&sql_id'
   and b.status = 'ACTIVE';

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

对应的alter system kill session的语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set linesize 260 pagesize 10000
col machine for a60
select machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;',
       status
  from v$session
 where sql_id = '&sql_id' and type='USER' and status='ACTIVE';

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7

被kill会话的类型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
set linesize 260 pagesize 10000
select b.osuser,b.machine,b.program,b.sql_id,b.PREV_SQL_ID,a.spid,to_char(LAST_CALL_ET) as seconds,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.inst_id=b.inst_id
   and a.background is null
   and b.type = 'USER'
   and b.event='&event_name'
   and b.status = 'ACTIVE';

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9

blocking会话类型和kill blocking会话：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
set linesize 260 pagesize 10000
col machine for a50
col kill_session for a60
SELECT
    blocking_instance,
    blocking_session,
    BLOCKING_SESSION_STATUS,
    FINAL_BLOCKING_INSTANCE,
    FINAL_BLOCKING_SESSION,
    COUNT(*)
FROM
    v$session
WHERE
    upper(event) LIKE '%&cursor%'
GROUP BY
    blocking_instance,
    blocking_session,
    BLOCKING_SESSION_STATUS,
    FINAL_BLOCKING_INSTANCE,
    FINAL_BLOCKING_SESSION
    order by blocking_instance,count(*);

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21

kill blocking会话：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
select 
       inst_id,
       machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
       status
  from gv$session a
where a.type='USER' and (a.inst_id,a.sid) in 
(
select 
BLOCKING_INSTANCE,
BLOCKING_SESSION 
from v$session
where upper(event) like '%&cursor%'
)
order by inst_id;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15

所有含有关键字“LOCAL=NO”的进程是Oracle数据库中远程连接进程的共同特点，因此通过以下命令可以kill掉所有的进程

1
2
3
4
5
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

AI写代码bash

* 1

本次分享到此结束啦~

如果觉得文章对你有帮助，点赞、收藏、关注、评论，一键四连支持，你的支持就是我创作最大的动力。

❤️
技术
交流可以 关注公众号：Lucifer三思而后行 ❤️