批量kill杀死某些会话session的PL/SQL

 

 

原文:

 

SQL> declare
2 v_sid v$session.sid%type; –定义如下两个type类型,用于接收cursor
v_serial# v$session.serial#%type;
3 4 cursor cur_session is select sid,serial# from v$session where program =’plsqldev.exe’; –#定义cursor
5 begin
6 open cur_session; –打开cusor
7 loop –打开游标马上开始循环,因为cursor是一条条取数据的
8 fetch cur_session into v_sid,v_serial#; –把游标的数据放入上面定义的type变量中
9 –根据以上的type变量及游标生成批量杀session的动态sql脚本,并执行
10 execute immediate ‘alter system kill session ”’||v_sid||’,’||v_serial#||”’ immediate’;
11 exit when cur_session%notfound; –要加个异常处理,不然永远是死循环
12 dbms_output.put_line(‘cursor date have been fetched ending’);
13 end loop; –loop也有成双匹配出现
14 close cur_session; –游标处理完后,关闭游标
15 end;
16 /
declare
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
ORA-06512: at line 10

 

明显杀会话时候,会话不存在。再执行类似的PL/SQL 块

SQL> SET serverout ON
SQL> DECLARE
2 v_ename EMP.ENAME%TYPE;
3 v_salary EMP.SAL%TYPE;
4 CURSOR c_emp IS SELECT ename,sal FROM emp;
5 BEGIN
6 OPEN c_emp;
7 loop
8 exit when c_emp%notfound;
9 FETCH c_emp INTO v_ename,v_salary;
10 DBMS_OUTPUT.PUT_LINE(‘Salary of Employee: ‘|| v_ename ||’ is ‘|| v_salary);
11 end loop;
12 CLOSE c_emp;
13 END ;
14 /
Salary of Employee: SMITH is 800
Salary of Employee: ALLEN is 1600
Salary of Employee: WARD is 1250
Salary of Employee: JONES is 2975
Salary of Employee: MARTIN is 1250
Salary of Employee: BLAKE is 2850
Salary of Employee: CLARK is 2450
Salary of Employee: SCOTT is 3000
Salary of Employee: KING is 5000
Salary of Employee: TURNER is 1500
Salary of Employee: ADAMS is 1100
Salary of Employee: JAMES is 4400
Salary of Employee: FORD is 3000
Salary of Employee: MILLER is 1300
Salary of Employee: MILLER is 1300

结果最后一行循环执行了2次,在杀会话plsql中,杀最后一个会话操作也执行了2次,所以会遇到报错。

 

调整PL/SQL 块语句,将exit when cur_session%notfound;  放在fetch 之后,也就是要循环执行的语句之前就解决了

 

declare
v_sid v$session.sid%type; –定义如下两个type类型,用于接收cursor
v_serial# v$session.serial#%type;
cursor cur_session is select sid,serial# from v$session where program =’plsqldev.exe’; –#定义cursor
begin
open cur_session; –打开cusor
loop –打开游标马上开始循环,因为cursor是一条条取数据的
fetch cur_session into v_sid,v_serial#; –把游标的数据放入上面定义的type变量中
exit when cur_session%notfound; –要加个异常处理,不然永远是死循环
–根据以上的type变量及游标生成批量杀session的动态sql脚本,并执行
execute immediate ‘alter system kill session ”’||v_sid||’,’||v_serial#||”’ immediate’;
dbms_output.put_line(‘cursor date have been fetched ending’);
end loop; –loop也有成双匹配出现
close cur_session; –游标处理完后,关闭游标
end;
/

 

SQL> select sid,serial#,status from v$session where program =’plsqldev.exe’;

SID         SERIAL#    STATUS
———- ———- ——–
26         27         INACTIVE
1159       189        INACTIVE

SQL>
SQL> declare
2 v_sid v$session.sid%type; –定义如下两个type类型,用于接收cursor
3 v_serial# v$session.serial#%type;
4 cursor cur_session is select sid,serial# from v$session where program =’plsqldev.exe’; –#定义cursor
5 begin
6 open cur_session; –打开cusor
7 loop –打开游标马上开始循环,因为cursor是一条条取数据的
8 fetch cur_session into v_sid,v_serial#; –把游标的数据放入上面定义的type变量中
9 exit when cur_session%notfound; –要加个异常处理,不然永远是死循环
10 –根据以上的type变量及游标生成批量杀session的动态sql脚本,并执行
11 execute immediate ‘alter system kill session ”’||v_sid||’,’||v_serial#||”’ immediate’;
12 dbms_output.put_line(‘cursor date have been fetched ending’);
13 end loop; –loop也有成双匹配出现
14 close cur_session; –游标处理完后,关闭游标
15 end;
16 /
cursor date have been fetched ending
cursor date have been fetched ending

PL/SQL procedure successfully completed.

SQL> select sid,serial#,status from v$session where program =’plsqldev.exe’;

no rows selected

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐