第74,75次：2014-12-21上下午

botang · 发表于 2014-12-22 17:12:23

本帖最后由 botang 于 2014-12-30 12:43 编辑

2014-12-21-A.sql：

select rpd.parallel_degree_limit_p1 , rpd.active_sess_pool_p1,rpd.undo_pool from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1'
and rpd.group_or_subplan='GROUP1';
select s.USERNAME, s.RESOURCE_CONSUMER_GROUP, status
from gv_$session s where s.TERMINAL='pts/16';
select i.index_name , i.degree
from dba_indexes i where i.owner='HR' and i.index_name='IBIG';
alter index hr.ibig noparallel;
drop index hr.ibig;
----
create table hr.t05316_a ( a char(2000));
insert into hr.t05316_a values ('A') ;
commit;
create table hr.t05316_b( a char(2000)) ;
insert into hr.t05316_b values (1);
commit;
------
select rpd.switch_group, rpd.switch_time, rpd.switch_for_call , rpd.switch_estimate from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1'
and rpd.group_or_subplan='GROUP1';
select s.USERNAME, s.RESOURCE_CONSUMER_GROUP, status
from gv_$session s where s.TERMINAL='pts/3';
drop index hr.ibig;
select rpd.parallel_degree_limit_p1 , rpd.active_sess_pool_p1,rpd.undo_pool ,rpd.queueing_p1 from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1'
and rpd.group_or_subplan='GROUP2';
alter user sh identified by oracle_4U account unlock;
select inst_id , sid , serial# status from gv$session where username='SH';
alter system kill session '56,1037,@1' immediate;
select * from gv$session where resource_consumer_group='GROUP2';
select s.USERNAME, s.RESOURCE_CONSUMER_GROUP, status
from gv_$session s where s.TERMINAL='pts/5';
drop index hr.ibig ;
select * from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1'
and rpd.group_or_subplan='GROUP1';
select s.USERNAME, s.RESOURCE_CONSUMER_GROUP, status
from gv_$session s where s.TERMINAL='pts/4';
select rpd.max_utilization_limit
from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1';
SELECT name FROM v$rsrc_plan
WHERE is_top_plan = 'TRUE' AND cpu_managed = 'ON';
SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
FROM v$rsrcmgrmetric_history
ORDER BY begin_time;
select * from V$RSRC_CONSUMER_GROUP ;
select * from dba_rsrc_group_mappings;
select * from dba_rsrc_mapping_priority;

复制代码

2014-12-21-B.sql：

select * from dba_services;
select * from gv$session where terminal='pts/11';
begin
dbms_service.modify_service(service_name => 'xexample',goal => dbms_Service.goal_service_time,aq_ha_notifications => true,
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 180,
failover_delay => 0);
end;
-----
select * from dba_rsrc_group_mappings;
select s.USERNAME , s.RESOURCE_CONSUMER_GROUP from gv_$session s where s.TERMINAL='pts/4';
select * from dba_rsrc_mapping_priority;
----
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(explicit =>1 ,
oracle_user =>10 ,
service_name =>3 ,
client_os_user =>4 ,
client_program =>5 ,
client_machine =>2,
module_name =>6 ,
module_name_action =>7 ,
service_module =>8 ,
service_module_action =>9 );
dbms_resource_manager.submit_pending_area();
end;
select * from v$servicemetric;

复制代码

2014-12-21-C.sql：

select * from dba_scheduler_programs;
select * from dba_sys_privs sp where sp.grantee='HR';
select * from dba_role_privs rp where rp.grantee='HR';
select * from role_sys_privs rsp where rsp.role='RESOURCE';
select distinct sp.privilege from dba_sys_privs sp where sp.privilege like '%JOB%';
grant CREATE EXTERNAL JOB to hr;
select * from dba_scheduler_credentials;
begin
dbms_scheduler.create_credential(credential_name => 'CREDENTIAL1',
username=>'oracle',password => 'oracle');
end;
select * from dba_scheduler_credentials;
grant execute on CREDENTIAL1 to hr;
select * from dba_tab_privs tp where tp.grantee='HR';
-------
select d.job_name,d.status ,d.error#, d.run_duration ,d.instance_id ,d.additional_info from dba_scheduler_job_run_details d ;
select * from dba_scheduler_jobs j where j.job_name='JOB3';

复制代码