1、如何释放 Oracle 服务器过多的连接数如何释放 Oracle 服务器过多的连接数Oracle 服务器连接数过多会当掉，把连接数过多的客户机网线拔出后，在远程 Oracle 上依然还会保留此用户的连接数，久久不能释放，上网查了下可以以下面方法解决。通过 profile 可以对用户会话进行一定的限制，比如 IDLE 时间。将 IDLE 超过一定时间的会话断开，可以减少数据库端的会话数量，减少资源耗用。使用这些资源限制特性，需要设置 resource_limit 为 TRUE：oracletest126 udump$ sqlplus “/ as sysdba”SQL*Plus: Release
2、10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining optionsSQL show parameter resourceNAME TYPE VALUE resource_limit boolean T
3、RUEresource_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 UNLIMITEDDEFAULT SESSIONS_PER_USER KERNEL UNLIMITEDDEFAULT CPU_PER_SESSION KERNEL UNL
4、IMITEDDEFAULT CPU_PER_CALL KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITEDDEFAULT IDLE_TIME KERNEL UNLIMITEDDEFAULT CONNECT_TIME KERNEL UNLIMITEDDEFAULT PRIVATE_SGA KERNEL UNLIMITEDDEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10DEFAULT PASS
5、WORD_LIFE_TIME PASSWORD UNLIMITEDPROFILE RESOURCE_NAME RESOURCE LIMIT DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITEDDEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULLDEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED16
6、 rows selected.创建一个允许 3 分钟 IDLE 时间的 PROFILE：SQL CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;Profile created.新创建 PROFILE 的内容：SQL col limit for a10SQL select * from dba_profiles where profile=KILLIDLE;PROFILE RESOURCE_NAME RESOURCE LIMIT -KILLIDLE COMPOSITE_LIMIT KERNEL DEFAULTKILLIDLE SESSIONS_PER_USER
7、 KERNEL DEFAULTKILLIDLE CPU_PER_SESSION KERNEL DEFAULTKILLIDLE CPU_PER_CALL KERNEL DEFAULTKILLIDLE LOGICAL_READS_PER_SESSION KERNEL DEFAULTKILLIDLE LOGICAL_READS_PER_CALL KERNEL DEFAULTKILLIDLE IDLE_TIME KERNEL 3KILLIDLE CONNECT_TIME KERNEL DEFAULTKILLIDLE PRIVATE_SGA KERNEL DEFAULTKILLIDLE FAILED_L
8、OGIN_ATTEMPTS PASSWORD DEFAULTKILLIDLE PASSWORD_LIFE_TIME PASSWORD DEFAULTPROFILE RESOURCE_NAME RESOURCE LIMIT -KILLIDLE PASSWORD_REUSE_TIME PASSWORD DEFAULTKILLIDLE PASSWORD_REUSE_MAX PASSWORD DEFAULTKILLIDLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULTKILLIDLE PASSWORD_LOCK_TIME PASSWORD DEFAULTKILLI
9、DLE PASSWORD_GRACE_TIME PASSWORD DEFAULT16 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=
10、EYGLE;USERNAME PROFILE EYGLE KILLIDLE进行连接测试：oracletest126 admin$ sqlplus eygle/eygleeygleSQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith
11、the Partitioning and Data Mining optionsSQL 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:41SQL 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
