第61和62次，2014-11-08， 053第13章和第16章

botang · 发表于 2014-11-10 11:32:48

本帖最后由 botang 于 2014-11-27 11:29 编辑

2014-11-08-A.txt：

select * from V$MEMORY_DYNAMIC_COMPONENTS
minus
select * from v$sga_dynamic_components;
select * from V$MEMORY_RESIZE_OPS ;
select * from V$MEMORY_TARGET_ADVICE ;
select * from v$sql where sql_id='dh73w3ss300hp';
select * from v$sqlarea where sql_id='dh73w3ss300hp' ;
select * from v$session where terminal='pts/4';
select * from dba_objects o where o.owner='HR' and o.object_type='PROCEDURE';
select * from v$db_object_cache where name ='SECURE_DML';
begin
dbms_shared_pool.keep('HR.SECURE_DML');
end;

复制代码

2014-11-08-B.txt：

select * from dba_rsrc_consumer_groups order by 2;
select * from dba_rsrc_consumer_group_privs;
select * from dba_rsrc_group_mappings;
select s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
from v_$session s where s.TERMINAL in ('pts/4','pts/13');
select * from dba_rsrc_plans;
select * from dba_rsrc_plan_directives pd where pd.plan='PLAN1';

复制代码

2014-11-08-C.txt：

select * from dba_rsrc_plan_directives pd where pd.plan='PLAN1' and pd.group_or_subplan='GROUP1';
select terminal, status , s.RESOURCE_CONSUMER_GROUP from v_$session s where s.RESOURCE_CONSUMER_GROUP='GROUP1' ;
select * from v$transaction;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',new_undo_pool => -1
);
dbms_resource_manager.submit_pending_area();
END;

复制代码

2014-11-08-D.txt：

select bytes/1024/1024 from dba_segments s where s.owner='HR' and s.segment_name='TBIG';
select * from dba_rsrc_plan_directives pd where pd.plan='PLAN1' and pd.group_or_subplan='GROUP1';
select terminal, status , s.RESOURCE_CONSUMER_GROUP from v_$session s where s.RESOURCE_CONSUMER_GROUP='GROUP1' ;
select * from v$transaction;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',new_switch_time => -1,new_switch_group => ''
);
dbms_resource_manager.submit_pending_area();
END;
select username, terminal, status , s.RESOURCE_CONSUMER_GROUP from v_$session s where s.TERMINAL='pts/3';
alter user sh identified by oracle_4U account unlock;
select username, terminal, status , s.RESOURCE_CONSUMER_GROUP from v_$session s where s.TERMINAL='pts/1';

复制代码

2014-11-08-E.txt：

select * from dba_rsrc_plans;
select * from dba_rsrc_plan_directives pd where pd.plan = 'PLAN2';
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 => 'SYS_GROUP',comment => '',
mgmt_p1 =>7
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'OTHER_GROUPS',comment => '',
mgmt_p1 =>3
);
dbms_resource_manager.submit_pending_area();
END;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'SYS_GROUP',
new_mgmt_p4 => 60
);
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'OTHER_GROUPS',
new_mgmt_p4 => 41
);
dbms_resource_manager.submit_pending_area();
END;
begin
dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',consumer_group1 => 'GROUP1',
group1_cpu => 75,consumer_group2 => 'GROUP2',group2_cpu => 25);
end;
select pd.group_or_subplan, pd.max_utilization_limit , pd.mgmt_p1 , pd.mgmt_p2, pd.mgmt_p3,pd.mgmt_p4
from dba_rsrc_plan_directives pd where pd.plan = 'PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'SYS_GROUP',new_max_utilization_limit => 99);
dbms_resource_manager.submit_pending_area();
END;
select pd.group_or_subplan, pd.max_utilization_limit , pd.mgmt_p1 , pd.mgmt_p2, pd.mgmt_p3,pd.mgmt_p4
from dba_rsrc_plan_directives pd where pd.plan = 'PLAN1';
SELECT name FROM v$rsrc_plan
WHERE is_top_plan = 'TRUE' AND cpu_managed = 'ON';
select * from dba_rsrc_mapping_priority;
select * from DBA_RSRC_GROUP_MAPPINGS;
select s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
from v_$session s where s.TERMINAL in ('pts/9');

复制代码