商城手机网站建设,手机网站域名m打头,甘肃张掖网站建设,专业创建网站需要定时kill非inactive session#xff0c;一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意#xff1a;1.v$session.statusSNIPED最好做清理#xff0c;2.未提交事务超时可…需要定时kill非inactive session一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意1.v$session.statusSNIPED最好做清理2.未提交事务超时可能强制回滚使用ORACLE PROFILE准备 SQL CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 1; Profile created. SQL select * from dba_profiles where profileKILLIDLE; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ------------ KILLIDLE COMPOSITE_LIMIT KERNEL DEFAULT KILLIDLE SESSIONS_PER_USER KERNEL DEFAULT KILLIDLE CPU_PER_SESSION KERNEL DEFAULT KILLIDLE CPU_PER_CALL KERNEL DEFAULT KILLIDLE LOGICAL_READS_PER_SESSION KERNEL DEFAULT KILLIDLE LOGICAL_READS_PER_CALL KERNEL DEFAULT KILLIDLE IDLE_TIME KERNEL 1 KILLIDLE CONNECT_TIME KERNEL DEFAULT KILLIDLE PRIVATE_SGA KERNEL DEFAULT KILLIDLE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT KILLIDLE PASSWORD_LIFE_TIME PASSWORD DEFAULT KILLIDLE PASSWORD_REUSE_TIME PASSWORD DEFAULT KILLIDLE PASSWORD_REUSE_MAX PASSWORD DEFAULT KILLIDLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT KILLIDLE PASSWORD_LOCK_TIME PASSWORD DEFAULT KILLIDLE PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected. SQL ALTER USER CHF PROFILE KILLIDLE; User altered. SQL SELECT USERNAME,PROFILE FROM DBA_USERS where usernameCHF; USERNAME PROFILE ------------------------------ ------------------------------ CHF KILLIDLE SQL SHOW PARAMETER resource_limit NAME TYPE VALUE ------------------------------------ ----------- --------------- resource_limit boolean FALSE SQL ALTER SYSTEM SET resource_limitTRUE; System altered.
如果要profile生效,需要修改resource_limittrue,IDLE_TIME单位为分钟测试IDLE_TIME --session 1 SQL show user; USER is CHF SQL select * from t_xifenfei; ID ---------- 1 --删除一条记录 SQL delete from t_xifenfei; 1 row deleted. --查询sid SQL select sid from v$mystat where rownum1; SID ---------- 20 --开始不操作该会话时间 SQL select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual; TO_CHAR(SYSDATE,YY ------------------- 2013-02-12 22:30:02 --session 2 SQL show user; USER is SYS --查询时间 SQL select status,to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from v$session where sid20; STATUS TO_CHAR(SYSDATE,YY -------- ------------------- INACTIVE 2013-02-12 22:31:00 --session 1 SQL select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual; select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual * ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again ----已经报会话超时 --session 2 SQL select status,to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from v$session where sid20; STATUS TO_CHAR(SYSDATE,YY -------- ------------------- SNIPED 2013-02-12 22:34:40 ----会话状态为sniped --session 1 SQL conn chf/xifenfei Connected. SQL select * from t_xifenfei; ID ---------- 1 ----事务回滚
SNIPED – An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again. 因为SNIPED的session只有当该session的终端发一个连接信息给数据库然后终端才会终止连接如果该客户端一直不发送类似访问则该连接一直存在数据库就很可能因为会话数目超过了数据库参数配置从而出现了ORA-00018错误业务不能正常运行.出现该问题可以通过如下脚本kill -9 pid解决kill SNIPED session 脚本 #!/bin/sh tmpfile/tmp/.kill_sniped sqlplus system/manager EOF spool $tmpfile select p.spid from v\$process p,v\$session s where s.paddrp.addr and s.statusSNIPED; spool off EOF for x in cat $tmpfile | grep ^[0123456789] do kill -9 $x done rm $tmpfile
另外补充说明IDLE_TIME是对于空闲时间超过了它的配置时间就会去强制终止会话,如果该会话中存在事务,但是inactive时间超过了IDLE_TIME配置时间数据库依然会强制终止会话并且回滚事务
