强制释放Oracle连接
强制释放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
|
评论暂时关闭