使用IDLE_TIME注意事项

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:使用IDLE_TIME注意事项

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

需要定时kill非inactive session,一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意:1.v$session.status=SNIPED最好做清理,2.未提交事务超时可能强制回滚
使用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单位为分钟

测试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 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/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.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile

另外补充说明,IDLE_TIME是对于空闲时间超过了它的配置时间就会去强制终止会话,如果该会话中存在事务,但是inactive时间超过了IDLE_TIME配置时间,数据库依然会强制终止会话,并且回滚事务

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

使用IDLE_TIME注意事项》有 1 条评论

  1. 惜分飞 说:

    kill inactive script

    #!/bin/sh
    tmpfile0=/tmp/.kill_inactive_0
    tmpfile1=/tmp/.kill_inactive_1
    tmpfile2=/tmp/.kill_inactive_2
    sqlplus / as sysdba <<EOF
    spool $tmpfile1
    select 'kill time:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') execute_time from dual;
    select p.spid,s.sid,s.serial# from v\$process p,v\$session s
    where s.paddr=p.addr
    and s.status='INACTIVE';
    spool off
    EOF
    cat $tmpfile1>>$tmpfile0
    grep "^[0123456789]" $tmpfile1 |awk '{print $1}'>$tmpfile2
    for x in `cat $tmpfile2`
    do
    kill -9 $x
    done
    rm $tmpfile1 $tmpfile2
    

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>