Oracle系统参数RESOURCE_LIMIT是一个用于控制用户对于数据库资源使用的参数，当值为true的时候即为启用，否则禁用。该参数结合pr
数据库性能是一个永恒的话题，那就是如何使用更少的资源以达到更高效的性能。Oracle系统参数RESOURCE_LIMIT是一个用于控制用户对于数据库资源使用的参数，当值为true的时候即为启用，否则禁用。该参数结合profile来可以控制多种资源的使用，，如CPU_PER_SESSION, CONNECT_TIME,LOGICAL_READS_PER_SESSION,
PRIVATE_SGA等等从而达到到节省资源来实现高效性能。本文描述了数据资源限制并演示了IDLE_TIME及SESSIONS_PER_USER的用法。
1、数据库资源限制的主要步骤
Implemented by
* Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
* Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit
* Assigning a profile to a user whose resources are wished to be limited
It could happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being set to 'sniped' and then getting 'cleaned up' via the shell script.
The removal of the dispatcher will result in other sessions 'dying' .In that case, If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT.
Alternatively, if you do change DEFAULT, ensure that all the properties that you have affected have been fully tested in a development environment.
用户超出限制后的完成的动作
When a resource limit is exceeded (for example IDLE_TIME) ... PMON does the following
* Mark the V$SESSION as SNIPED
* Clean up the database resources for the session
* Remove the V$SESSION entry
2、资源限制的配置
--演示环境
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--查看参数resource_limit
SQL> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
--修改参数resource_limit为true
SQL> alter system set resource_limit=true;
System altered.
SQL> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
--创建profile，其idle_time为3分钟
SQL> create profile app_user limit idle_time 3;
Profile created.
--修改profile，限制每个用户只能开一个session
SQL> alter profile app_user limit sessions_per_user 1;
Profile altered.
--将用户指派给特定的profile
SQL> alter user scott profile app_user;
User altered.
--查看刚刚创建的profile，查询结果中的RESOURCE_NAME都可以作相应的设置或修改
SQL> select * from dba_profiles where profile='APP_USER';
配置文件 RESOURCE_NAME 资源限制
-------------------------------- ---------- ---------------------- -------------------- -------------------- --------------------
APP_USER COMPOSITE_LIMIT 内核默认
APP_USER SESSIONS_PER_USER 1
APP_USER CPU_PER_SESSION 内核默认
APP_USER CPU_PER_CALL 内核 默认
APP_USER LOGICAL_READS_PER_SESSION 内核默认
APP_USER LOGICAL_READS_PER_CALL 内核默认
APP_USER IDLE_TIME KERNEL 3
APP_USER CONNECT_TIME DEFAULT
APP_用户 PRIVATE_SGA 内核默认
APP_USER FAILED_LOGIN_ATTEMPTS 密码默认
APP _USER PASSWORD_LIFE_TIME 密码默认
APP_USER PASSWORD_REUSE_TIME 密码默认值
APP_USER PASSWORD_REUSE_MAX 密码默认值
APP_USER PASSWORD_VERIFY_FUNCTION 密码默认
APP_USER PASSWORD_LOCK_TIME 密码默认
APP_USER PASSWORD_GRACE_TIME 密码默认
已选择 16 行。
