一. 开发背景#
做kill session操作时需要考虑session 占用的回滚段大小.本程序所有查询结果均排除username为null的会话。
二. 流程图#
![]() |
三. 参数说明#
1. 函数头#
function kill_session(kill varchar2 default 'false', filter_name varchar2 default null, filter_word varchar2 default null, used_undo_below number default 250, session_id number default 0, serial number default 0) return t_session_info_tab
pipelined;
2. 参数#
该参数决定是否执行kill session的操作,对哪些会话执行kill操作由其他参数的过滤条件决定。
该值为'true'时,执行kill操作.
该值为'true'时,执行kill操作.
四 举例 #
1、以dbuser做过滤条件,kill session。#
SQL> select * from table(dba_session.kill_session('true', 'dbuser','dbmgr'));
SID SERIAL USED_UNDO USERNAME STATUS SQL_HASH_VALUE OSUSER PROGRAM EVENT KILLED ---- ---------- ---------- ---------- -------- -------------- ------------------------------ ------------------------------------------------ ------------------------- ------ 93 15 0 DBMGR KILLED 0 lianghaian001 plsqldev.exe SQL*Net message from clie YES
nt
97 14 0 DBMGR KILLED 0 lianghaian001 plsqldev.exe SQL*Net message from clie YES
2、显示当前event为'SQL*Net'开头的会话信息。#
SQL> select * from table(dba_session.kill_session(null, 'event','SQL*Net%'));
SID SERIAL USED_UNDO USERNAME STATUS SQL_HASH_VALUE OSUSER PROGRAM EVENT KILLED ---- ---------- ---------- ---------- -------- -------------- ------------------------------ ------------------------------------------------ ------------------------- ------ 14 28 0 COW_SUNXIN INACTIVE 0 sunxin005 plsqldev.exe SQL*Net message from clie NO
005 nt
25 362 0 LIANGHAIAN ACTIVE 2749853118 lianghaian001 plsqldev.exe SQL*Net message from clie NO
001 nt
26 273 0 COW_WUXIAO INACTIVE 0 wuxiaoli133 plsqldev.exe SQL*Net message from clie NO
LI133 nt