Oracle服务器连接数过多会当掉把连接数过多的客户机网线拔出后在远程Oracle上依然还会保留此用户的连接数久久不能释放上网查了下可以以下面方法解决
通过profile可以对用户会话进行一定的限制比如IDLE时间
将IDLE超过一定时间的会话断开可以减少数据库端的会话数量减少资源耗用
使用这些资源限制特性需要设置resource_limit为TRUE
[oracle@test udump]$ sqlplus / as sysdba
SQL*Plus: Release Production on Fri Oct ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition Release 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
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
rows selected
创建一个允许分钟IDLE时间的PROFILE
SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME ;
Profile created
新创建PROFILE的内容
SQL> col limit for a
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
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
rows selected
测试用户
SQL> select usernameprofile from dba_users where username=EYGLE;
USERNAME PROFILE
—————————— ——————–
EYGLE DEFAULT
修改eygle用户的PROFILE使用新建的PROFILE
SQL> alter user eygle profile killidle;
User altered
SQL> select usernameprofile from dba_users where username=EYGLE;
USERNAME PROFILE
—————————— ——————–
EYGLE KILLIDLE
进行连接测试
[oracle@test admin]$ sqlplus eygle/eygle@eygle
SQL*Plus: Release Production on Fri Oct ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition Release Production
With the Partitioning and Data Mining options
SQL> select usernameprofile from dba_users where username=EYGLE;
USERNAME PROFILE
—————————— ——————————
EYGLE KILLIDLE
当IDLE超过限制时间时连接会被断开
SQL> select to_char(sysdateyyyymmdd hh:mi:ss) from dual;
TO_CHAR(SYSDATEYY
——————
::
SQL> select to_char(sysdateyyyymmdd hh:mi:ss) from dual;
select to_char(sysdateyyyymmdd hh:mi:ss) from dual
*
ERROR at line :
ORA: exceeded maximum idle time please connect again
