关于Oracle PROFILE的详细用法请参见文章《【PROFILE】使用Oracle的PROFILE对用户资源限制和密码限制的研究与探索》(http://space.itpub.net/519536/viewspace-616287)。
本文给出使用PROFILE实现限制具体用户会话中的调用所使用的CPU时间。
1.创建VERIFY_FUNCTION函数
sys@ora10g> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
2.创建名为SEC_PROFILE的PROFILE
sys@ora10g> CREATE PROFILE sec_profile LIMIT
2 CPU_PER_CALL 500
3 /
Profile created.
规则是:会话中一次调用的CPU时间不能超过5秒!
3.查看SEC_PROFILE定义信息
sys@ora10g> col PROFILE for a12
sys@ora10g> col RESOURCE_NAME for a25
sys@ora10g> col LIMIT for a15
sys@ora10g> select * from dba_profiles where profile = 'SEC_PROFILE' order by 1,2;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------ ------------------------- -------- ---------------
SEC_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
SEC_PROFILE CONNECT_TIME KERNEL DEFAULT
SEC_PROFILE CPU_PER_CALL KERNEL 500
SEC_PROFILE CPU_PER_SESSION KERNEL DEFAULT
SEC_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
SEC_PROFILE IDLE_TIME KERNEL DEFAULT
SEC_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
SEC_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
SEC_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
SEC_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
SEC_PROFILE PRIVATE_SGA KERNEL DEFAULT
SEC_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
16 rows selected.
4.调整resource_limit参数使PROFILE的限制生效
sys@ora10g> show parameter resource_limit
NAME TYPE VALUE
----------------- -------------------- ------------
resource_limit boolean FALSE
sys@ora10g> alter system set resource_limit=true;
System altered.
sys@ora10g> show parameter resource_limit
NAME TYPE VALUE
----------------- -------------------- ------------
resource_limit boolean TRUE
5.将新创建的PROFILE SEC_PROFILE指定给具体用户SEC
sys@ora10g> alter user sec profile sec_profile;
User altered.
sys@ora10g> select USERNAME,PROFILE from dba_users where USERNAME = 'SEC';
USERNAME PROFILE
------------------------------ ------------
SEC SEC_PROFILE
6.模拟长时间无法完成的调用
sec@ora10g> EXEC DBMS_STATS.GATHER_DATABASE_STATS;
BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02393: exceeded call limit on CPU usage
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_UTILITY"
ORA-06512: at "SYS.DBMS_STATS", line 13206
ORA-02393: exceeded call limit on CPU usage
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1
这里分析语句在执行5秒钟后报错,此处的“ORA-02393: exceeded call limit on CPU usage”报错信息已经清楚地指出了我们所做的限制。
依此类推,SEC用户下所有超过5秒的操作都会遭遇此类报错。
7.小结
当遭遇由于PROFILE限制导致具体操作无法完成的问题时,我们有两种处理方法,一种是彻底关掉PROFILE功能(最简便的方法就是将resource_limit参数调整为FALSE);另外一种方法便是细致的调整PROFIE定义内容到合适值。
Good luck.
secooler
11.08.04
-- The End --
本文给出使用PROFILE实现限制具体用户会话中的调用所使用的CPU时间。
1.创建VERIFY_FUNCTION函数
sys@ora10g> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
2.创建名为SEC_PROFILE的PROFILE
sys@ora10g> CREATE PROFILE sec_profile LIMIT
2 CPU_PER_CALL 500
3 /
Profile created.
规则是:会话中一次调用的CPU时间不能超过5秒!
3.查看SEC_PROFILE定义信息
sys@ora10g> col PROFILE for a12
sys@ora10g> col RESOURCE_NAME for a25
sys@ora10g> col LIMIT for a15
sys@ora10g> select * from dba_profiles where profile = 'SEC_PROFILE' order by 1,2;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------ ------------------------- -------- ---------------
SEC_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
SEC_PROFILE CONNECT_TIME KERNEL DEFAULT
SEC_PROFILE CPU_PER_CALL KERNEL 500
SEC_PROFILE CPU_PER_SESSION KERNEL DEFAULT
SEC_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
SEC_PROFILE IDLE_TIME KERNEL DEFAULT
SEC_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
SEC_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
SEC_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
SEC_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
SEC_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
SEC_PROFILE PRIVATE_SGA KERNEL DEFAULT
SEC_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
16 rows selected.
4.调整resource_limit参数使PROFILE的限制生效
sys@ora10g> show parameter resource_limit
NAME TYPE VALUE
----------------- -------------------- ------------
resource_limit boolean FALSE
sys@ora10g> alter system set resource_limit=true;
System altered.
sys@ora10g> show parameter resource_limit
NAME TYPE VALUE
----------------- -------------------- ------------
resource_limit boolean TRUE
5.将新创建的PROFILE SEC_PROFILE指定给具体用户SEC
sys@ora10g> alter user sec profile sec_profile;
User altered.
sys@ora10g> select USERNAME,PROFILE from dba_users where USERNAME = 'SEC';
USERNAME PROFILE
------------------------------ ------------
SEC SEC_PROFILE
6.模拟长时间无法完成的调用
sec@ora10g> EXEC DBMS_STATS.GATHER_DATABASE_STATS;
BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02393: exceeded call limit on CPU usage
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_UTILITY"
ORA-06512: at "SYS.DBMS_STATS", line 13206
ORA-02393: exceeded call limit on CPU usage
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1
这里分析语句在执行5秒钟后报错,此处的“ORA-02393: exceeded call limit on CPU usage”报错信息已经清楚地指出了我们所做的限制。
依此类推,SEC用户下所有超过5秒的操作都会遭遇此类报错。
7.小结
当遭遇由于PROFILE限制导致具体操作无法完成的问题时,我们有两种处理方法,一种是彻底关掉PROFILE功能(最简便的方法就是将resource_limit参数调整为FALSE);另外一种方法便是细致的调整PROFIE定义内容到合适值。
Good luck.
secooler
11.08.04
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-704216/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-704216/