模拟场景:当突然发现cpu使用率陡然升高,而不知道是谁进行的操作,可以通过top命名查看对应的pid(cpu使用率陡然升高的用户会话),然后查询对应会话的sid,serial#,最后和业务部门等联系看看能不能杀掉此会话,能就杀,不能CPU高也没办法,业务需要。
实验:
一:在虚拟机内执行查询语句:
1:会话1:执行select * from dba_objects;
执行此查询语句,进程可能一致在跑,CPU上升可能很多;
2:会话2:linux下操作系统上执行top,查看pid(即跑会话1的那个进程的pid)
[oracle@wang ~]$ top
top - 20:25:37 up 2 days, 6:47, 3 users, load average: 0.06,
Tasks: 207 total, 3 running, 204 sleeping, 0 stopped, 0 z
Cpu(s): 34.0%us, 14.0%sy, 0.0%ni, 46.0%id, 0.0%wa, 0.0%hi,
Mem: 2035900k total, 1885036k used, 150864k free, 30748
Swap: 4192956k total, 145892k used, 4047064k free, 1108936
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
9372 oracle 20 0 72024 13m 9864 S 2.7 0.7 0:00.98
8747 root 20 0 90144 3380 2624 S 2.0 0.2 0:01.60
14022 oracle 20 0 128m 11m 6532 R 1.0 0.6 0:00.03
873 oracle -2 0 1035m 16m 14m S 0.7 0.8 4:23.98
891 oracle 20 0 1035m 20m 17m S 0.3 1.0 0:21.57
2918 root 20 0 128m 1288 928 S 0.3 0.1 1:31.22
3.会话3查询,通过会话2查询的pid查询会话1的sid、serial#:
SYS@ORA11GR2>select t.sid,t.serial#,d.sql_text, t.program, t.process from v$sqlarea d, v$session t where d.address = t.sql_address and d.hash_value = t.sql_hash_value and process = '9372';
SID SERIAL# SQL_TEXT PROGRAM PROCESS
---------------- ---------------------------- ---------------------------- ----------
47 3597 select * from dba_objects sqlplus@wang (TNS V1-V3) 9372
——杀死会话1:
SYS@ORA11GR2>alter system kill session '47,3597'immediate;
System altered.
此时发现会话1已经断开了(not connected to ORACLE)
二:在windows客户端,执行命令(操作同理)
1.在windowns下执行:
2:会话2:linux下执行top,查看pid
[oracle@wang ~]$ top
top - 20:44:56 up 2 days, 7:07, 4 users, load average: 0.43,
Tasks: 211 total, 2 running, 209 sleeping, 0 stopped, 0 z
Cpu(s): 71.2%us, 26.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi,
Mem: 2035900k total, 1885736k used, 150164k free, 32764
Swap: 4192956k total, 145888k used, 4047068k free, 1110164
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
15907 oracle 20 0 1036m 26m 23m R 95.3 1.3 0:19.86
923 oracle 20 0 1037m 31m 28m S 0.7 1.6 1:02.56
873 oracle -2 0 1035m 16m 14m S 0.3 0.8 4:29.75
2746 root 20 0 126m 2568 1640 S 0.3 0.1 5:15.02
3950 root 20 0 123m 6240 3516 S 0.3 0.3 3:06.61
15429 oracle 20 0 12756 1172 812 R 0.3 0.1 0:00.10
29523 oracle -2 0 1261m 12m 12m S 0.3 0.6 10:09.32
1 root 20 0 10364 452 420 S 0.0 0.0 0:07.80
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00
3 root RT 0 0 0 0 S 0.0 0.0 0:00.0
3.会话3:
SYS@ORA11GR2>select t.sid,t.serial#,d.sql_text, a.spid, t.program, t.process
2 from v$sqlarea d, v$session t, v$process a
3 where d.address = t.sql_address
4 and d.hash_value = t.sql_hash_value
5 and t.paddr = a.addr
6 and a.spid in (15907);
SID SERIAL# SQL_TEXT SPID
---------- ---------- ---------------------------- ------------------------
PROGRAM PROCESS
---------------------------- ----------
62 2649 SELECT SYSDATE FROM DUAL 15907
sqlplus.exe 8688:10520
——杀死会话1:
SYS@ORA11GR2>alter system kill session '62,2649'immediate;
alter system kill session '62,2649'immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
验证:
1.SID 会话标识符
2.SERIAL# 会话序列号, 用于唯一标识会话的对象。 如果会话结束并且另一个会话以相同的会话ID开始,则保证会话级命令应用于正确的会话对象。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126468/