[原创] 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7364032/viewspace-448453/