Oracle连接数过多释放机制

Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。
  通过profile可以对用户会话进行一定的限制,比如IDLE时间。

  将IDLE超过一定时间的会话断开,可以减少数据库端的会话数量,减少资源耗用。

  使用这些资源限制特性,需要设置resource_limit为TRUE:

  [oracle@test126 udump]$ sqlplus "/ as sysdba"

  SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006

  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

  Connected to:

  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

  With the Partitioning and Data Mining options

  SQL> show parameter resource

  NAME                                TYPE        VALUE


  ------------------------------------ ----------- ------------------------------

  resource_limit                      boolean    TRUE

  resource_manager_plan                string

  该参数可以动态修改:

  SQL> alter system set resource_limit=true;

  System altered.


  数据库缺省的PROFILE设置为:


  SQL> SELECT * FROM DBA_PROFILES; 转自:全球学习网

  PROFILE              RESOURCE_NAME                    RESOURCE LIMIT 转自:全球学习网

  -------------------- -------------------------------- -------- --------------- 转自:全球学习网

  DEFAULT              COMPOSITE_LIMIT                  KERNEL  UNLIMITED 转自:全球学习网

  DEFAULT              SESSIONS_PER_USER                KERNEL  UNLIMITED


  DEFAULT              CPU_PER_SESSION                  KERNEL  UNLIMITED

  DEFAULT              CPU_PER_CALL                    KERNEL  UNLIMITED


  DEFAULT              LOGICAL_READS_PER_SESSION        KERNEL  UNLIMITED 转自:全球学习网

  DEFAULT              LOGICAL_READS_PER_CALL          KERNEL  UNLIMITED

  DEFAULT              IDLE_TIME                        KERNEL  UNLIMITED

  DEFAULT              CONNECT_TIME                    KERNEL  UNLIMITED

  DEFAULT              PRIVATE_SGA                      KERNEL  UNLIMITED

  DEFAULT              FAILED_LOGIN_ATTEMPTS            PASSWORD 10

  DEFAULT              PASSWORD_LIFE_TIME              PASSWORD UNLIMITED

  PROFILE              RESOURCE_NAME                    RESOURCE LIMIT
DEFAULT              PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

  DEFAULT              PASSWORD_REUSE_MAX              PASSWORD UNLIMITED

  DEFAULT              PASSWORD_VERIFY_FUNCTION        PASSWORD NULL

  DEFAULT              PASSWORD_LOCK_TIME              PASSWORD UNLIMITED

  DEFAULT              PASSWORD_GRACE_TIME              PASSWORD UNLIMITED



  16 rows selected.

  创建一个允许3分钟IDLE时间的PROFILE:


  SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;

  Profile created.


  新创建PROFILE的内容:

  SQL> col limit for a10

  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  3
KILLIDLE                      CONNECT_TIME                    KERNEL  DEFAULT

  KILLIDLE                      PRIVATE_SGA                      KERNEL  DEFAULT

  KILLIDLE                      FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT

  KILLIDLE                      PASSWORD_LIFE_TIME              PASSWORD DEFAULT

  PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

  ------------------------------ -------------------------------- -------- ----------

  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> select username,profile from dba_users where username='EYGLE';


  USERNAME                      PROFILE

  ------------------------------ --------------------

  EYGLE                          DEFAULT



  修改eygle用户的PROFILE使用新建的PROFILE:

  SQL> alter user eygle profile killidle;


  User altered.

  SQL> select username,profile from dba_users where username='EYGLE';

  USERNAME                      PROFILE
 ------------------------------ --------------------


  EYGLE                          KILLIDLE


  进行连接测试:

  [oracle@test126 admin]$ sqlplus eygle/eygle@eygle

  SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006

  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

  Connected to:

  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

  With the Partitioning and Data Mining options

  SQL> select username,profile from dba_users where username='EYGLE';

  USERNAME                      PROFILE

  ------------------------------ ------------------------------

  EYGLE                          KILLIDLE


  当IDLE超过限制时间时,连接会被断开:

  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  TO_CHAR(SYSDATE,'YY

  -------------------


  2006-10-13 08:08:41

  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

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页