熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle連接數過多釋放機制

2013-11-13 16:00:21  來源: 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


From:http://tw.wingwit.com/Article/program/Oracle/201311/17659.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.