关于Oracle PROFILE的详细用法请参见文章《【PROFILE】使用Oracle的PROFILE对用户资源限制和密码限制的研究与探索》(http://space.itpub.net/519536/viewspace-616287)。本文给出使用PROFILE实现限制具体用户会话中的调用所使用的CPU时间。1.创建VERIFY_FUNCTION函数sys@ora10g> @?/rdbms/admin/utlpwdmg.sqlFunction created.Profile altered.2.创建名为SEC_PROFILE的PROFILEsys@ora10g> CREATE PROFILE sec_profile LIMIT2 CPU_PER_CALL 5003 /Profile created.规则是:会话中一次调用的CPU时间不能超过5秒!3.查看SEC_PROFILE定义信息sys@ora10g> col PROFILE for a12sys@ora10g> col RESOURCE_NAME for a25sys@ora10g> col LIMIT for a15sys@ora10g> select * from dba_profiles where profile = 'SEC_PROFILE' order by 1,2;PROFILE RESOURCE_NAME RESOURCE LIMIT------------ ------------------------- -------- ---------------SEC_PROFILE COMPOSITE_LIMIT KERNEL DEFAULTSEC_PROFILE CONNECT_TIME KERNEL DEFAULTSEC_PROFILE CPU_PER_CALL KERNEL 500SEC_PROFILE CPU_PER_SESSION KERNEL DEFAULTSEC_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULTSEC_PROFILE IDLE_TIME KERNEL DEFAULTSEC_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULTSEC_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULTSEC_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULTSEC_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULTSEC_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULTSEC_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULTSEC_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULTSEC_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULTSEC_PROFILE PRIVATE_SGA KERNEL DEFAULTSEC_PROFILE SESSIONS_PER_USER KERNEL DEFAULT16 rows selected.4.调整resource_limit参数使PROFILE的限制生效sys@ora10g> show parameter resource_limitNAME TYPE VALUE----------------- -------------------- ------------resource_limit boolean FALSEsys@ora10g> alter system set resource_limit=true;System altered.sys@ora10g> show parameter resource_limitNAME TYPE VALUE----------------- -------------------- ------------resource_limit boolean TRUE5.将新创建的PROFILE SEC_PROFILE指定给具体用户SECsys@ora10g> alter user sec profile sec_profile;User altered.sys@ora10g> select USERNAME,PROFILE from dba_users where USERNAME = 'SEC';USERNAME PROFILE------------------------------ ------------SEC SEC_PROFILE6.模拟长时间无法完成的调用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 1ORA-02393: exceeded call limit on CPU usageORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_UTILITY"ORA-06512: at "SYS.DBMS_STATS", line 13206ORA-02393: exceeded call limit on CPU usageORA-06512: at "SYS.DBMS_STATS", line 13556ORA-06512: at "SYS.DBMS_STATS", line 13700ORA-06512: at "SYS.DBMS_STATS", line 13664ORA-06512: at line 1这里分析语句在执行5秒钟后报错,此处的“ORA-02393: exceeded call limit on CPU usage”报错信息已经清楚地指出了我们所做的限制。依此类推,SEC用户下所有超过5秒的操作都会遭遇此类报错。7.小结当遭遇由于PROFILE限制导致具体操作无法完成的问题时,我们有两种处理方法,一种是彻底关掉PROFILE功能(最简便的方法就是将resource_limit参数调整为FALSE);另外一种方法便是细致的调整PROFIE定义内容到合适值。Good luck.secooler11.08.04-- The End --
Oracle 会话超500就,【PROFILE】使用Oracle PROFILE限制会话中每一次调用所使用的CPU资源...
最新推荐文章于 2022-01-17 07:24:25 发布