课程第11次

secoug · 发表于 2019-10-16 20:46:22

本帖最后由 secoug 于 2019-10-16 22:33 编辑

select * from dba_users u where u.username='HR';
select * from dba_rsrc_consumer_groups;
select * from dba_rsrc_consumer_group_privs;
select * from dba_rsrc_plan_directives pd where pd.plan='PLAN1';
select * from dba_rsrc_plans;
--'RATIO'
select s."USERNAME",s."SID",s."SERIAL#",s."RESOURCE_CONSUMER_GROUP"
from v$session s where s."TERMINAL"='pts/0';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'PLAN2', comment => 'PLAN2',cpu_mth => 'RATIO' );
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'OTHER_GROUPS',
comment => 'OTHER_GROUPS',
mgmt_p1 => 5
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP1',
comment => 'GROUP1',
mgmt_p1 => 90
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP2',
comment => 'GROUP2',
mgmt_p1 => 5
);
dbms_resource_manager.submit_pending_area();
END;
select * from dba_rsrc_plan_directives pd where pd.plan='PLAN2';
select * from dba_rsrc_plans;

复制代码

定位会话和Dedicate连接的服务器进程的信息：

SQL> show parameter resource_manager_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string PLAN1
SQL> select spid from v$process p , v$session s
2 where p.addr=s.paddr and
3 s.terminal='pts/0';
SPID
------------------------
30292
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station79 ~]$ ps aux | grep 30292

复制代码

select s."USERNAME",s."SID",s."SERIAL#",s."RESOURCE_CONSUMER_GROUP",s."TERMINAL", s."STATUS"
from v$session s where s."USERNAME"='HR';
select t."STATUS"
from v$transaction t;
select * from dba_indexes i
where i.table_owner='HR' and i.table_name in ('TBIG','TBIG2');
select * from dba_rsrc_plan_directives pd where pd.plan='PLAN1' and pd.group_or_subplan='GROUP1';

复制代码