数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

Oracle连接数过多释放机制


发布日期:2021年04月11日
 
Oracle连接数过多释放机制

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

               

上一篇:八个学习点帮助你全面认识Oracle数据库

下一篇:在Oracle中实现各种日期处理完全版