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

设置允许session 空闲时间

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

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

System altered.

设置默认profile的 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

 3） 执行脚本，并查看结果

[oracle@centos1 ~]$ sh kill_sniped_session.sh 

[oracle@centos1 backup]$ more kill_successfull12-08-15.log
kill sniped session at 12-08-15 successfull; and total sessions:10

最后

以上就是美好飞机为你收集整理的设置 会话的空闲时间 idle_time,清理 SNIPED session的全部内容，希望文章能够帮你解决设置 会话的空闲时间 idle_time,清理 SNIPED session所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。