模拟开发人员需求,可以杀死其它阻塞自己的会话
1.能有查询阻塞会话确认的权限
SQL> grant select on v_$session to testa;
SQL> grant select on v_$sql to testa;
SQL> select sid,serial#,username,sql_id,status,event,BLOCKING_INSTANCE,BLOCKING_SESSION from v$session where username=‘TESTA‘;
SID SERIAL# USERNAME SQL_ID STATUS EVENT BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ---------- -------------- ---------- ------------------------------ ----------------- ----------------
8 13 TESTA a49v0mry3c0rk ACTIVE SQL*Net message to client
191 5 TESTA INACTIVE SQL*Net message from client
197 5 TESTA 96vz122hk9mtq ACTIVE enq: TX - row lock contention 1 191
查询确认自己被阻塞
SQL> select sql_fulltext from v$sql where sql_id=‘96vz122hk9mtq‘;
SQL_FULLTEXT
--------------------------------------------------------------------------------
delete a
2.杀会话权限alter system
SQL> grant alter system to testa;
SQL> alter system kill session ‘191,5‘ immediate;
权限太大，不合理。
观察是否能kill后台进程权限
$ps -ef|grep pmon
oracle 5185 1 0 20:36 ? 00:00:00 ora_pmon_tt11204
SQL>select spid,pid,username ,program ,background,addr from v$process where spid=5185;
SPID PID USERNAME PROGRAM BA ADDR
---------- ---------- -------------------- -------------------- -- ----------------
5185 2 oracle oracle@enmo (PMON) 1 00000000F550BE60
SQL>select sid,serial#,username,event,program from v$session where paddr=‘00000000F550BE60‘;
SID SERIAL# USERNAME EVENT PROGRAM
---------- ---------- ---------- ------------------------------ --------------------
125 1 pmon timer oracle@enmo (PMON)
SQL> alter system kill session ‘125,1‘ immediate;
alter system kill session ‘125,1‘ immediate
*
ERROR at line 1:
ORA-00029: session is not a user session
会话无法kill非用户,但是SYS权限大的用户会话也可以kill
SQL> show user
USER is "TESTA"
SQL> select sid,serial#,username from v$session where username=‘SYS‘;
SID SERIAL# USERNAME
---------- ---------- ----------
198 43 SYS
SQL> alter system kill session ‘198,43‘ immediate;
System altered.
后台进程需要通过os层面kill
不会kill后台进程造成实例层面崩溃，但是可能对其它正常执行的用户造成影响，不可控。测试环境可以，生产环境不建议授权。
开发人员需求能kill杀死其它阻塞自己的会话，测试发现需要alter system权限有风险
原文：https://www.cnblogs.com/lvcha001/p/11297195.html