12cCLOUDCONTROL使用方法和多租户体系结构，课程第66/67次（2017-03-19星期日上下午）

botang · 发表于 2017-3-20 10:23:28

上完1Z0-063第1章（12cCLOUDCONTROL使用方法）
上完1Z0-063第2章（多租户体系结构）
开始1Z0-063第3章（各种方法创建多租户数据库）
1Z0-052共19章（上完19章），1Z0-053共21章（上完21章），1Z0-063多租户部分共9章（上完2章）
总共上完全部49章中的42章

select * from dba_rsrc_plan_directives d
where d.PLAN='PLAN1' and d.GROUP_OR_SUBPLAN='GROUP1';
---
SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
FROM v$rsrcmgrmetric_history
ORDER BY begin_time;
---
begin
dbms_resource_manager.create_pending_area;
dbms_resource_manager.set_consumer_group_mapping('Client_machine','station37.example.com','group2');
dbms_resource_manager.submit_pending_area;
end;
---
select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS;
begin
dbms_resource_manager.clear_pending_area;
dbms_resource_manager.create_pending_area;
dbms_resource_manager_privs.grant_system_privilege('HR','ADMINISTER_RESOURCE_MANAGER',false);
dbms_resource_manager.submit_pending_area;
end;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
EXPLICIT => 1, CLIENT_MACHINE => 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_OS_USER => 10,
CLIENT_ID => 11
);
dbms_resource_manager.submit_pending_area();
END;

复制代码

多租户：

select name, cdb, con_id from v$database;
select INSTANCE_NAME, STATUS, CON_ID from v$instance;
select name , con_id from v$services;
select name , open_mode , con_id from v$pdbs;
alter pluggable database pdb1_1 open;
select sys_context('userenv','con_name') from dual;
select sys_context('userenv','con_id') from dual;
select pdb_id, dbid, guid ,pdb_name , con_id from cdb_pdbs;
---
select name ,con_id from v$datafile
where con_id=3;
select name ,con_id from v$datafile
where con_id=2;
---
select * from v$log;
select * from v$logfile;
select * from v$controlfile;
select file_name, tablespace_name, file_id , con_id
from cdb_data_files
order by con_id , tablespace_name;
select name , con_id from v$datafile
where con_id=1;
select * from dba_data_files;
---
select d.name, file#, t.name , t.ts# , t.con_id
from v$tablespace t, v$datafile d
where t.ts# = d.ts#
and t.con_id= d.con_id;
---
select file_name, tablespace_name , file_id , con_id
from cdb_temp_files;
select name, file# ,con_id from
v$tempfile;
select username, common, con_id from cdb_users
where username='SYSTEM';
select username, common, con_id from cdb_users
where username='SYS';
select username, common, con_id from cdb_users
where username='HR';
select username, common, con_id from cdb_users
where con_id=1
order by username;
select username, common, con_id from cdb_users
where con_id=3
order by username;
select username, common, con_id from cdb_users
where con_id=3 and common='NO'
order by username;
select username from cdb_users
where con_id=3
minus
select username from cdb_users
where con_id=1 ;
----
select role, common, con_id from cdb_roles
where con_id=1
order by role;
select role, common, con_id from cdb_roles
where con_id=3
order by role;
---
select *
from cdb_sys_privs
where con_id=1
;
select *
from cdb_sys_privs
where con_id=3;
select s.GRANTEE, s.PRIVILEGE, s.COMMON
from cdb_sys_privs s
where con_id=3
minus
select s.GRANTEE, s.PRIVILEGE, s.COMMON
from cdb_sys_privs s
where con_id=1;
----
select *
from cdb_tab_privs
where con_id=1 and
common='NO'
;
select count(*)
from cdb_tab_privs
where con_id=1 and
common='YES'
;
select *
from cdb_tab_privs
where con_id=3 and
common='NO'
;
select count(*)
from cdb_tab_privs
where con_id=3 and
common='YES'
;
---
select *
from cdb_role_privs
where con_id=1 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=1 and
common='YES'
;
select *
from cdb_role_privs
where con_id=3 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=3 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=3 and
common='NO'
;

复制代码

select name, cdb, con_id from v$database;
select INSTANCE_NAME, STATUS, CON_ID from v$instance;
select name , con_id from v$services;
select name , open_mode , con_id from v$pdbs;
alter pluggable database pdb1_1 open;
select sys_context('userenv','con_name') from dual;
select sys_context('userenv','con_id') from dual;
select pdb_id, dbid, guid ,pdb_name , con_id from cdb_pdbs;
---
select name ,con_id from v$datafile
where con_id=3;
select name ,con_id from v$datafile
where con_id=2;
---
select * from v$log;
select * from v$logfile;
select * from v$controlfile;
select file_name, tablespace_name, file_id , con_id
from cdb_data_files
order by con_id , tablespace_name;
select name , con_id from v$datafile
where con_id=1;
select * from dba_data_files;
---
select d.name, file#, t.name , t.ts# , t.con_id
from v$tablespace t, v$datafile d
where t.ts# = d.ts#
and t.con_id= d.con_id;
---
select file_name, tablespace_name , file_id , con_id
from cdb_temp_files;
select name, file# ,con_id from
v$tempfile;
select username, common, con_id from cdb_users
where username='SYSTEM';
select username, common, con_id from cdb_users
where username='SYS';
select username, common, con_id from cdb_users
where username='HR';
select username, common, con_id from cdb_users
where con_id=1
order by username;
select username, common, con_id from cdb_users
where con_id=3
order by username;
select username, common, con_id from cdb_users
where con_id=3 and common='NO'
order by username;
select username from cdb_users
where con_id=3
minus
select username from cdb_users
where con_id=1 ;
----
select role, common, con_id from cdb_roles
where con_id=1
order by role;
select role, common, con_id from cdb_roles
where con_id=3
order by role;
---
select *
from cdb_sys_privs
where con_id=1
;
select *
from cdb_sys_privs
where con_id=3;
select s.GRANTEE, s.PRIVILEGE, s.COMMON
from cdb_sys_privs s
where con_id=3
minus
select s.GRANTEE, s.PRIVILEGE, s.COMMON
from cdb_sys_privs s
where con_id=1;
----
select *
from cdb_tab_privs
where con_id=1 and
common='NO'
;
select count(*)
from cdb_tab_privs
where con_id=1 and
common='YES'
;
select *
from cdb_tab_privs
where con_id=3 and
common='NO'
;
select count(*)
from cdb_tab_privs
where con_id=3 and
common='YES'
;
---
select *
from cdb_role_privs
where con_id=1 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=1 and
common='YES'
;
select *
from cdb_role_privs
where con_id=3 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=3 and
common='YES'
;
select count(*)
from cdb_role_privs
where con_id=3 and
common='NO'
;

复制代码