天萃荷净
使用IDLE_TIME注意事项,通过设置profile中的idle_time来实现定时kill非inactive session
需要定时kill非inactive session,一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意:1.v$session.status=SNIPED最好做清理,2.未提交事务超时可能强制回滚
1.使用ORACLE PROFILE准备
SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 1;
Profile created.
SQL> select * from dba_profiles where profile='KILLIDLE';
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 username='CHF';
USERNAME PROFILE
------------------------------ ------------------------------
CHF KILLIDLE
SQL> SHOW PARAMETER resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ---------------
resource_limit boolean FALSE
SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.
如果要profile生效,需要修改resource_limit=true,IDLE_TIME单位为分钟
2.测试IDLE_TIME
--session 1
SQL> show user;
USER is "CHF"
SQL> select * from t_oracleplus;
ID
----------
1
--删除一条记录
SQL> delete from t_oracleplus;
1 row deleted.
--查询sid
SQL> select sid from v$mystat where rownum=1;
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 sid=20;
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 sid=20;
STATUS TO_CHAR(SYSDATE,'YY
-------- -------------------
SNIPED 2013-02-12 22:34:40
----会话状态为sniped
--session 1
SQL> conn chf/oracleplus
Connected.
SQL> select * from t_oracleplus;
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解决
3.kill SNIPED session 脚本
#!/bin/sh
tmpfile=/tmp/.kill_sniped
sqlplus system/manager
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle profile中的idle_time使用案例与注意事项