设置会话的空闲时间 idle_time,清理 SNIPED session

本文介绍如何在Oracle数据库中配置会话空闲超时，包括启用resource_limit参数、设置默认profile的idle_time限制及编写清理SNIPED状态会话的脚本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

设置允许session 空闲时间

首先要启用 resource_limit参数，默认是false;

SQL> alter system set resource_limit=true scope=both;

System altered.

设置默认profile的 idle_time 10分钟

SQL> alter profile default limit idle_time 10;

Profile altered.

需要重启数据库才能生效

idle_time Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.

查询应用的连接数

SELECT b.MACHINE, b.PROGRAM, COUNT (*)

FROM v$process a, v$session b
WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;

在OS上写kill 脚本清理状态为 SNIPED session

1）查询脚本

[oracle@centos1 ~]$ more sniped_session.sql
rem sniped_session.sql
rem DESCRIPTION
rem kill sniped session
rem MODIFIED
set pagesize 1000
set heads off
set verify off
set heading off
set termout off
set echo off
set feedback off
spool on
spool /opt/app/sql/kill_sniped_session.lst
select 'kill -9 '||spid UNIX_level_kill
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status = 'SNIPED'
AND last_call_et > 60 * 10 ----10分钟
ORDER BY last_call_et desc;
spool off

2）编写kill 脚本

[oracle@centos1 ~]$ more kill_sniped_session.sh
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<!
@sniped_session.sql
exit
!
if [ -s /opt/app/sql/kill_sniped_session.lst ]
then
echo "have a list of sniped_session"
grep kill /opt/app/sql/kill_sniped_session.lst
grep kill /opt/app/sql/kill_sniped_session.lst | awk '{ print $3 }' | xargs kill -9 2>/opt/app/backup/kill_sniped_session.log
fi
if [ $? -ne 0 ]
then
echo "kill sniped session at `date +%y-%m-%d` failure " > /opt/app/backup/kill_failure`date +%y-%m-%d`.log
else
sessions_count=`grep kill /opt/app/sql/kill_sniped_session.lst | wc -l`
echo "kill sniped session at `date +%y-%m-%d` successfull; and total sessions:${sessions_count}" > /opt/app/backup/kill_successfull`date +%y-%m-%d`.log
fi