课程第46/47次（2018-05-20星期日上下午）

botang

CDB2：

1. select name from v$services;
   
2. 
   
3. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
   
4. from v_$tablespace t , v_$datafile d
   
5. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
   
6. 
   
7. order by 1;
   
8. 
   
9. --------------
   
10. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
    
11. from v_$tablespace t , v_$tempfile d
    
12. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
    
13. 
    
14. order by 1;
    
15. 
    
16. ---------
    
17. 
    
18. select  con_id, name , open_mode from v$pdbs;
    
19. 
    
20. drop pluggable database pdb2_1 including datafiles;
    
21. 
    
22. alter pluggable database pdb2_1 close immediate;
    
23. 
    
24. ------------------
    
25. 
    
26. create pluggable database pdb2_2 from pdb2_1
    
27. file_name_convert=('/u01/app/oracle/oradata/cdb2/pdb2_1',
    
28.     '/u01/app/oracle/oradata/cdb2/pdb2_2');
    
29.    
    
30.   ---------------------
    
31.   select  con_id, name ,  open_mode from v$pdbs;
    
32.   
    
33.   alter pluggable database pdb2_2 open;
    
34.   
    
35.   -----------------------
    
36. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
    
37. from v_$tablespace t , v_$datafile d
    
38. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
    
39. order by 1;
    
40. -----------------------
    
41. alter pluggable database pdb2_2 close immediate;
    
42. 
    
43. drop pluggable database  pdb2_2  including datafiles;
    
44. -----
    
45. alter system set db_create_file_dest='/u01/app/oracle/oradata/cdb2/pdb2_2';
    
46. 
    
47. create pluggable database pdb2_2 from pdb2_1;
    
48. -----
    
49. select  * from cdb_pdbs;
    
50. 
    
51. 
    

复制代码

PDB2_1：

1. create user hr identified by oracle_4U  ;
   
2. 
   
3. grant connect , resource to hr ;
   
4. 
   
5. select  * from cdb_sys_privs  sp where sp.grantee='HR';
   
6. 
   
7. select  * from cdb_tab_privs  tp where tp.grantee='HR';
   
8. 
   
9. select  * from cdb_role_privs  rp where rp.grantee='HR';
   
10. 
    
11. select  * from role_sys_privs  rsp where rsp.role='RESOURCE';
    
12. 
    
13. create tablespace users
    
14. datafile '/u01/app/oracle/oradata/cdb2/pdb2_1/users01.dbf' size 20M ;
    
15. 
    
16. alter user hr default tablespace users quota unlimited on  users;
    
17. 
    
18. select  * from cdb_ts_quotas  tq;
    
19. 
    
20. select open_mode from v$database;

复制代码

PDB2_2：

1. select  open_mode from v$database;
   
2. 
   
3. alter database open;
   
4. 
   
5. select  * from hr.t06303_a;
   
6. 
   
7. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
   
8. from v_$tablespace t , v_$datafile d
   
9. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
   
10. order by 1;
    
11. 
    
12. --------------
    
13. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
    
14. from v_$tablespace t , v_$tempfile d
    
15. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
    
16. 
    
17. order by 1;
    

复制代码

PDB_ORCL2（与1Z0-063书上不同）：

1. select  con_id, name , open_mode from v$pdbs;
   
2. 
   
3. create pluggable database pdb_orcl2  using
   
4. '/home/oracle/orcl2.xml'
   
5. file_name_convert=( '+DATA/ORCL2/DATAFILE/system.266.976031403',
   
6.                                  '/u01/app/oracle/oradata/cdb2/pdb_orcl2/system01.dbf',
   
7.                                 '+DATA/ORCL2/DATAFILE/sysaux.269.976031405',
   
8.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/sysaux01.dbf',
   
9.                                 '+DATA/ORCL2/DATAFILE/users.268.976031411',
   
10.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/users01.dbf',
    
11.                                 '+DATA/ORCL2/DATAFILE/example.267.976031415',
    
12.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/example01.dbf',
    
13.                                 '+DATA/ORCL2/TEMPFILE/temp.259.976031881',
    
14.                                  '/u01/app/oracle/oradata/cdb2/pdb_orcl2/temp01.dbf'                                
    
15. );
    
16. 
    
17. select  con_id, name , open_mode from v$pdbs;

复制代码

CDB1拔出PDB1_1：

1. select name, open_mode from v$pdbs;
   
2. 
   
3. alter pluggable database pdb1_1 unplug into '/home/oracle/pdb1_1.xml';
   
4. 
   
5. alter pluggable database pdb1_1 close ;
   
6. 
   
7. alter pluggable database pdb1_1 unplug into '/home/oracle/pdb1_1.xml';
   
8. 
   
9. select  * from cdb_pdbs;
   
10. 
    
11. drop pluggable database pdb1_1 keep datafiles;
    
12. 
    
13. 
    

复制代码

CDB2插入PDB1_1：

1. DECLARE
   
2.   compat BOOLEAN := FALSE;
   
3. BEGIN
   
4. compat := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
   
5. pdb_descr_file =>'/home/oracle/pdb1_1.xml',
   
6. pdb_name => 'pdb1_1');
   
7. if compat
   
8.   then
   
9.   DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? YES');
   
10. else
    
11.    DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? NO');
    
12.   end if;
    
13. end;
    
14. 
    
15. select  * from pdb_plug_in_violations;
    
16. 
    
17. create pluggable database pdb1_1 using  '/home/oracle/pdb1_1.xml' nocopy;
    
18. 
    
19. select  con_id, name ,  open_mode from v$pdbs;
    
20. 
    
21. alter pluggable database  pdb1_1  open;

复制代码

带着插件库删除根容器：

1. select name, open_mode from v$pdbs;
   
2. 
   
3. create pluggable database dog admin  user pdbadmin
   
4. identified by oracle_4U roles=(connect);
   
5. 
   
6. alter pluggable database dog open;

复制代码

启动和关闭插件数据库：

alter pluggable database xxx close和alter pluggable database xxx  close immediate的区别：前者有会话连接就关闭不掉。

在close的插件数据库上还能够用SYS来连接。

插件数据库的信息只有在mount的时候才能看（插件的物理结构在控制文件里），数据字典在各自的SYSTEM/SYSAUX表空间里。

1. alter pluggable database pdb2_1 close immediate;
   
2. 
   
3. select con_id, name , open_mode from v$pdbs;
   
4. 
   
5. alter pluggable database pdb2_1 open;
   
6. 
   
7. alter pluggable database pdb2_1 close;
   
8. 
   
9. select  * from dba_triggers;
   
10. 
    
11. create trigger open_all_pdbs
    
12.   after startup on database
    
13.   begin
    
14.    execute immediate 'alter pluggable database all open';
    
15.   end;
    
16.   
    
17.   select  * from dba_source s where s.OWNER='SYS' and s.NAME='OPEN_ALL_PDBS';

复制代码

关于插件数据库restricted session：

1. SQL> conn sys/oracle_4U@pdb2_1 as sysdba
   
2. Connected.
   
3. SQL> alter pluggable database  rename global_name to pdb2;
   
4. alter pluggable database  rename global_name to pdb2
   
5.                                                 *
   
6. ERROR at line 1:
   
7. ORA-65045: pluggable database not in a restricted mode
   
8. 
   
9. 
   
10. SQL> shutdown immediate
    
11. Pluggable Database closed.
    
12. SQL> alter pluggable database open restricted;
    
13. 
    
14. Pluggable database altered.
    
15. 
    
16. SQL> alter pluggable database  rename global_name to pdb2;
    
17. 
    
18. Pluggable database altered.
    
19. 
    
20. SQL> select con_id, name , open_mode from v$pdbs;
    
21. 
    
22.     CON_ID NAME                           OPEN_MODE
    
23. ---------- ------------------------------ ----------
    
24.          3 PDB2                           READ WRITE
    
25. 
    
26. SQL> alter system disable restricted session ;
    
27. 
    
28. System altered.
    
29. 
    
30. SQL>
    

复制代码

有些参数在插件上能改，有些不能改：

1. select ISPDB_MODIFIABLE from v$parameter
   
2. where name='optimizer_use_sql_plan_baselines';
   
3. 
   
4. 
   
5. select ISPDB_MODIFIABLE from v$parameter
   
6. where name='control_files';

复制代码

看同一个参数的不同的值，请查v$system_parameter;

每一个插件数据库的个性化参数应该各自的数据字典有关系：

1. SQL> startup nomount
   
2. ORACLE instance started.
   
3. 
   
4. Total System Global Area 4294967296 bytes
   
5. Fixed Size                    2932632 bytes
   
6. Variable Size                 2583691368 bytes
   
7. Database Buffers         1694498816 bytes
   
8. Redo Buffers                   13844480 bytes
   
9. select CON_ID, VALUE from
   
10.   2  V$SYSTEM_PARAMETER
    
11.   3  where name ='optimizer_use_sql_plan_baselines';
    
12. 
    
13.     CON_ID
    
14. ----------
    
15. VALUE
    
16. --------------------------------------------------------------------------------
    
17.          0
    
18. TRUE
    
19. 
    
20. 
    
21. SQL> alter database mount;
    
22. 
    
23. Database altered.
    
24. 
    
25. SQL> set linesize 10000
    
26. SQL> select CON_ID, VALUE from
    
27.   2  V$SYSTEM_PARAMETER
    
28.   3  where name ='optimizer_use_sql_plan_baselines';
    
29. 
    
30.     CON_ID VALUE
    
31. ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
32.          0 TRUE
    
33. 
    
34. SQL> alter database open ;
    
35. 
    
36. Database altered.
    
37. 
    
38. SQL> select CON_ID, VALUE from
    
39.   2  V$SYSTEM_PARAMETER
    
40.   3  where name ='optimizer_use_sql_plan_baselines';
    
41. 
    
42.     CON_ID VALUE
    
43. ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
44.          0 TRUE
    
45.          3 FALSE
    
46. 
    
47. SQL>
    

复制代码

PDB_ORC2（从外面插进来的普通12c的数据库）没有继承到的根容器的通用用户：



根容器上的新的通用用户的继承性关系：




新的创建用户的语法（container=ALL）：

1. create user c##u identified by x;
   
2. 
   
3. select  c.CON_ID, c.USERNAME,
   
4.     c.COMMON,
   
5.     c.DEFAULT_TABLESPACE,
   
6.    c.TEMPORARY_TABLESPACE
   
7. from cdb_users c
   
8.   where c.USERNAME='C##U'
   
9.   order by 1;
   
10.   
    
11.   create user c##_user identified by x container=all;
    
12.   
    
13.   select  c.CON_ID, c.USERNAME,
    
14.     c.COMMON,
    
15.     c.DEFAULT_TABLESPACE,
    
16.    c.TEMPORARY_TABLESPACE
    
17. from cdb_users c
    
18.   where c.USERNAME='C##_USER'
    
19.   order by 1;

复制代码