sqlnet.expire_time and idle_time

sqlnet.expire_time and idle_time

当设置了resource_limit=true 。通过idle_time限制session idle 时间。session idle超过设置时间，状态为sniped (v$session).,然而OS下的process并不会释放，当session(user process) 再次与server process 通讯，将关闭相应的server process sqlnet.expire_time 的原理不一样，Oracle Server 发送包探测dead connection ，如果连接关闭，或者不再用，则关闭相应的server process. 以上两者组合使用，减少server process,防止process超过init$ORACLE_SID极限值。 DCD: Dead Connection Detection ，可以用于检测、标记僵死而没有断开会session，再由PMON进行清理，释放资源。 开启DCD，只需要在服务端的sqlnet.ora文件中添加SQLNET.EXPIRE_TIME参数，单位为分钟： SQLNET.EXPIRE_TIME=10 如果时间达到这个值，server端就是发出一个”probe” packet 给客户端，如要客户断是正常的，这个packet就被忽略，timer重新计时；如果客户端异常中断，则server端就会收到一个消息，用以释放连接。 #查找长时间不用的session. SELECT s.username,s.status,s.machine,osuser,spid, 'kill -9 '||spid UNIX_level_kill, 'alter system kill session ' ||''''||s.sid||','||s.serial# || ''';' Oracle_level_kill, TO_CHAR (logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time, last_call_et idle_time, TO_CHAR (TRUNC (last_call_et / 3600, 0))||' '||' HRS '||TO_CHAR (TRUNC ((last_call_et -TRUNC(last_call_et / 3600, 0) * 3600) / 60, 0)) ||' MINS' idle_time_hour_minute, module FROM v$session s, v$process p WHERE TYPE = 'USER' AND p.addr = s.paddr AND status = 'SNIPED' -- AND SUBSTR (machine, 1, 19) NOT IN ('machine') AND last_call_et > 60 * 60 * 2 -- session idle time more than 1 hour ORDER BY last_call_et desc; ##写了一个脚本,kill sniped session ##kill_sniped_session.sh #! /bin/bash ORACLE_SID=xxxxprod export ORACLE_SID ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME SQLPATH=/apps/oracle/sql export SQLPATH # $ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<! @sniped_session.sql exit ! if [ -s /apps/oracle/sql/kill_sniped_session.lst ] then echo "have a list of sniped_session" grep kill /apps/oracle/sql/kill_sniped_session.lst grep kill /apps/oracle/sql/kill_sniped_session.lst | awk '{ print $3 }' | xargs kill -9 2>/backup/oracle/kill_sniped_session.log fi if [ $? -ne 0 ] then cat /backup/oracle/kill_sniped_session.log | mailx -s "xxxxprod kill sniped session failed" else sessions_count=`grep kill /apps/oracle/sql/kill_sniped_session.lst | wc -l` echo "sessions:${sessions_count}" | mailx -s "xxxxprod kill sniped session successful"  touch /backup/oracle/kill_sniped_session.sh fi  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 /apps/oracle/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 * 60 * 3 ORDER BY last_call_et desc; spool off ##btw What does 'SNIPED' status in v$session mean? When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora. This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session sqlnet.expire_time sqlnet.expire_time actually works on a different principle and is used to detect dead connectionsas opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. 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. But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.