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 |
