课程第46/47次（2017-07-01星期六上下午）

botang · 发表于 2017-7-1 15:58:47

上完1Z0-053第16章Resource Manager
进行1Z0-052第7章
1Z0-052共19章（上完13章），1Z0-053共21章（上完17章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的30章

TOAD连接12c：

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_active_sess_pool_p1 => -1,
new_queueing_p1 => -1
);
dbms_resource_manager.submit_pending_area();
END;
select * from dba_rsrc_plan_directives d where d.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 => 'GROUP1',
new_undo_pool => 8
);
dbms_resource_manager.submit_pending_area();
END;
select * from dba_rsrc_plan_directives d where d.PLAN='PLAN1' and
d.GROUP_OR_SUBPLAN='GROUP1';
select s.USERNAME,s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
from v$session s
where s.TERMINAL in ('pts/2','pts/3')
;
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='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 => 'GROUP1',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => NULL ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => 'GROUP2',
new_switch_time => NULL,
new_switch_estimate =>true,
new_undo_pool => NULL ,
new_max_idle_time => NULL,
new_max_idle_blocker_time => NULL,
new_switch_for_call => true
);
dbms_resource_manager.submit_pending_area();
END;
---
select * from dba_rsrc_plan_directives d where d.PLAN='PLAN1'
and d.GROUP_OR_SUBPLAN='GROUP1';
----
SELECT value FROM
v$parameter WHERE
name = 'cpu_count' AND (isdefault = 'FALSE' OR ismodified != 'FALSE');
----
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 name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;
----
select * from dba_rsrc_group_mappings;
----
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.client_os_user,
'oracle',
'GROUP2'
);
dbms_resource_manager.submit_pending_area();
END;
----
select * from dba_rsrc_mapping_priority;
---
select s.USERNAME,s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
from v$session s
where s.TERMINAL in ('pts/2','pts/3')
;
----
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
EXPLICIT => 1, CLIENT_OS_USER => 2,
SERVICE_MODULE_ACTION => 3,
SERVICE_MODULE => 4,
MODULE_NAME_ACTION => 5,
MODULE_NAME => 6,
SERVICE_NAME => 7,
ORACLE_USER => 8,
CLIENT_PROGRAM => 9,
CLIENT_MACHINE => 10,
CLIENT_ID => 11
);
dbms_resource_manager.submit_pending_area();
END;
---
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, SERVICE_MODULE_ACTION => 2,
SERVICE_MODULE => 3,
MODULE_NAME_ACTION => 4,
MODULE_NAME => 5,
SERVICE_NAME => 6,
ORACLE_USER => 7,
CLIENT_OS_USER => 8,
CLIENT_PROGRAM => 9,
CLIENT_MACHINE => 10,
CLIENT_ID => 11
);
dbms_resource_manager.submit_pending_area();
END;
----

复制代码

PL/SQL连接11g:

select * from dba_rsrc_plan_directives p
where p.plan='DEFAULT_MAINTENANCE_PLAN';
select * from dba_rsrc_plan_directives p
where p.plan='ORA$AUTOTASK_SUB_PLAN';
select s.USERNAME,s.SID,s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.TERMINAL='pts/15';
select * from dba_rsrc_plan_directives pd
where pd.plan='PLAN1' and pd.group_or_subplan='GROUP1';
----------
------------
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 => 8192
);
dbms_resource_manager.submit_pending_area();
END;
----
SELECT value FROM
v$parameter WHERE
name = 'cpu_count' AND (isdefault = 'FALSE' OR ismodified != 'FALSE');
----
SELECT name FROM v$rsrc_plan
WHERE is_top_plan = 'TRUE' AND cpu_managed = 'ON';
---
dba_rsrc_group_mappings
---
dba_rsrc_mapping_priority;

复制代码

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_active_sess_pool_p1 => 1,
new_queueing_p1 => 5
);
dbms_resource_manager.submit_pending_area();
END;
select * from dba_rsrc_plan_directives d where d.PLAN='PLAN1'
and d.GROUP_OR_SUBPLAN='GROUP1';
select s.USERNAME,s.SID,s.RESOURCE_CONSUMER_GROUP, terminal
from v_$session s
where s.TERMINAL in ('pts/2', 'pts/3');

复制代码

关于表空间：

select * from dba_segments s
where s.SEGMENT_NAME='EMPLOYEES'
and s.OWNER='HR';
select * from dba_extents s
where s.SEGMENT_NAME='EMPLOYEES'
and s.OWNER='HR';
select * from dba_tables t
where t.TABLE_NAME='EMPLOYEES' and t.OWNER='HR';
select i.PCT_FREE from dba_indexes i
where i.TABLE_NAME='EMPLOYEES' and i.OWNER='HR'
and i.index_name='EMP_NAME_IX';
---
---
select * from dba_segments s
where s.SEGMENT_NAME='EMP_NAME_IX'
and s.OWNER='HR';
-----------------
select * from dba_extents s
where s.SEGMENT_NAME='EMP_NAME_IX'
and s.OWNER='HR';
-----
CREATE SMALLFILE TABLESPACE "TBS1"
DATAFILE '/u01/app/oracle/oradata/orcl/tbs1' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 1G
LOGGING
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
( INITIAL 512K NEXT 512K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 150) MINIMUM EXTENT 512K;
---
select * from dba_tablespaces t
where t.TABLESPACE_NAME in ('TBS1','USERS');
----
select * from dba_extents e
where e.SEGMENT_NAME='T04209_UNAME' and e.OWNER='HR';
----
select sum(f.BYTES)/1024/1024
from dba_data_files f
where f.TABLESPACE_NAME='SYSTEM';
select sum(s.BYTES)/1024/1024
from dba_free_space s
where s.TABLESPACE_NAME='SYSTEM';
select sum(f.BYTES)/1024/1024
from dba_data_files f
where f.TABLESPACE_NAME='UNDOTBS1';
select sum(s.BYTES)/1024/1024
from dba_free_space s
where s.TABLESPACE_NAME='UNDOTBS1';
select sum(e.BYTES)/1024/1024
from dba_undo_extents e
where e.TABLESPACE_NAME='UNDOTBS1'
and e.STATUS <> 'EXPIRED';
-------
select sum(f.BYTES)/1024/1024
from dba_temp_files f
where f.TABLESPACE_NAME='TEMP';
-----------
select (t.bytes_used)/1024/1024 from V$TEMP_EXTENT_POOL t;

复制代码

做映射：

begin
dbms_resource_manager.clear_pending_area;
dbms_resource_manager.create_pending_area;
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.client_machine,'192.168.0.37','group2');
dbms_resource_manager.submit_pending_area;
end;

复制代码