[原創] Oracle資料庫資源管理

1.建立一個掛起區域：

SQL> exec dbms_resource_manager.create_pending_area;

PL/SQL procedure successfully completed.

2.建立資源規劃

SQL> exec dbms_resource_manager.create_plan(plan =>'test_plan',COMMENT=>'this is testing plan') ;

PL/SQL procedure successfully completed.

3.建立使用者組

SQL> exec dbms_resource_manager.create_consumer_group( consumer_group => 'demo', comment => 'yes it is a demo' );

PL/SQL procedure successfully completed.

4.指定資源規劃指令

SQL> exec dbms_resource_manager.create_plan_directive( plan => 'test_plan',group_or_subplan => 'demo',comment => 'its a demo',MAX_EST_EXEC_TIME => 5 );

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_plan_directive( plan => 'test_plan',group_or_subplan => 'OTHER_GROUPS', comment => 'its a demo' );

PL/SQL procedure successfully completed.

5.驗證變化

SQL>  exec  dbms_resource_manager.validate_pending_area;

PL/SQL procedure successfully completed.

6.提交
SQL> exec dbms_resource_manager.submit_pending_area;

PL/SQL procedure successfully completed.

7.建立一個使用者

SQL> drop user user1 cascade;

User dropped.

SQL> create user user1 identified by abc123;

User created.

SQL> grant connect, resource to user1;

Grant succeeded.

8.授予切換許可權,其實就並將資源規則分配給這個使用者
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group ( 'user1', 'demo', TRUE );

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group ( 'user1', 'demo' );

PL/SQL procedure successfully completed.

9.啟動資料庫資源管理器

SQL>  alter system set resource_manager_plan = test_plan scope = memory;

System altered.

10.使用者登入檢驗設定
SQL> conn user1/abc123;
Connected.
SQL> create table t as select * from all_objects;

Table created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select count(*) from t;

  COUNT(*)
----------
     40138

SQL> select count(*) from t,t;
select count(*) from t,t
                     *
ERROR at line 1:
ORA-07455: estimated execution time (59781 secs), exceeds limit (5 secs)

可以看到,已經被限制了.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-448453/，如需轉載，請註明出處，否則將追究法律責任。