« 史上最强黑屏行动-微软20日黑屏反盗版验证 | Blog首页 | Oracle10gR2如何删除误添加的空文件 »
使用Profile对用户Session会话进行资源限制
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2008/10/profile_session_limit.html
通过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
历史上的今天...
>> 2007-10-18文章:
>> 2006-10-18文章:
>> 2005-10-18文章:
>> 2004-10-18文章:
------
这篇 【使用Profile对用户Session会话进行资源限制】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2008-10-18 12:17 | Comments (2) | Posted to FAQ | HowTo | Edit |Pageviews:
搜索本站:
留言 (2)
不错。
Posted by: wzknet at October 19, 2008 12:01 PM
但是如果使用了连接池之类的东东，就会出些问题，比如前台连接不上之类的
Posted by: onlybear at October 20, 2008 9:53 AM
