详谈Oracle12c新特点容器数据库&可插拔数据库（CDB&PDB）

一般信息

数据字典	CDB_FILE$ DBA_PDBS PDB$SEED CDB_LOCAL_ADMINAUTH$ DBA_PDB_HISTORY PDB_HISTORY$ CDB_PDB_SAVED_STATES DBA_PDB_SAVED_STATES PDB_PLUG_IN_VIOLATIONS CDB_RESOURCE_PLAN$ DBMS_PDB PDB_SPFILE$ CDB_RESOURCE_PLAN_DIRECTIVE$ DBMS_PDB_EXEC_SQL V$CONTAINERS CDB_SERVICE$ GV$CONTAINERS V$PDBS CDB_HIST_PDB_INSTANCE GV$PDBS V$PDB_INCARNATION CDB_PDBS GV$PDB_INCARNATION WRI$_ADV_ADDM_PDBS CDB_PDB_HISTORY GV$SESSIONS_COUNT WRM$_PDB_INSTANCE CONTAINERS INT$DBA_PDB_SAVED_STATES XDB$CDBPORTS DBA_HIST_PDB_INSTANCE PDB_ALERT$
角色	CDB_DBA
系统权限	CREATE PLUGGABLE DATABASE SET CONTAINER SET CONTAINER
探究SEED PDB
从PDB$SEED创建PDB	语法： CREATE PLUGGABLE DATABASE ADMIN USER IDENTIFIED BY FILE_NAME_CONVERT = ('', '') ROLES = () DEFAULT TABLESPACE [DATAFILE ] EXTENT MANAGEMENT LOCAL <autoallocate |="" uniform="" size="" > STORAGE ];   例子：
	col con_name format a10 col restricted format a10 SELECT v.name, v.open_mode, v.restricted, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn; NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED                       READ ONLY  NO         NORMAL   ALTER PLUGGABLE DATABASE pdb1 OPEN; SELECT v.name, v.open_mode, v.restricted, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn; NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED                       READ ONLY  NO         NORMAL   SELECT * FROM dba_pdbs;   -- 12.1.0.1 version -- SELECT * FROM cdb$view(dba_pdbs); -- 12.1.0.2 versionSELECT * FROM containers(dba_pdbs); -- 12.1.0.1 version -- SELECT * FROM cdb$view(cdb_pdbs); -- 12.1.0.2 version SELECT * FROM containers(cdb_pdbs);   SELECT table_name, tablespace_name, con_id FROM containers(dba_tables) WHERE rownum < 6;
	CREATE PLUGGABLE DATABASE pdb1 ADMIN USER TEST IDENTIFIED BY TEST                                                                                                                       FILE_NAME_CONVERT = ('/pdbseed/', '/pdb1/');   SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v1; NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB1                         MOUNTED    NO         NORMAL PDB$SEED                       READ ONLY  NO         NORMAL   ALTER SESSION SET CONTAINER=pdb1;   sho con_name CON_NAME ------------------- PDB1   ALTER PLUGGABLE DATABASE pdb1 OPEN; SELECT COUNT(*)FROM dba_tables;
探究数据文件	desc dba_data_files desc cdb_data_files   SELECT file_name, tablespace_name, online_status FROM dba_data_files;   SELECT file_name, tablespace_name, online_status, con_id FROM cdb_data_files;
探究用户	desc dba_users desc cdb_users   SELECT username, account_status, lock_date, expiry_date FROM dba_users ORDER BY 1;   SELECT username, default_tablespace, temporary_tablespace FROM dba_users ORDER BY 1;   SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users ORDER BY 1;   SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users WHERE username like 'TE%' ORDER BY 1;   ALTER PLUGGABLE DATABASE pdb1 OPEN;   SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;   NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED                       READ ONLY  NO         NORMAL PDB1 READ WRITE NO         NORMAL   SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users WHERE username like 'TE%' ORDER BY 1;
连接创建PDB	SQL> conn sys@pdb1 as sysdba Enter password: *********Connected.   SQL> GRANT create session TO TEST; Grant succeeded.   SQL> SELECT COUNT(*) FROM cdb_sys_privs; COUNT(*) 0   SQL> SELECT COUNT(*) FROM dba_sys_privs; COUNT(*) 987   SQL> SELECT privilege, admin_option, common   FROM dba_sys_privs  WHERE grantee = 'TEST';   PRIVILEGE                                ADM COM ---------------------------------------- --- --- CREATE SESSION                           NO  NO   col grantee format a30 col granted_role format a30   SELECT * FROM dba_role_privs WHERE grantee = 'TEST'; GRANTEE                        GRANTED_ROLE                   ADM DEF COM ------------------------------ ------------------------------ --- --- --- TEST                        PDB_DBA                        YES YES NO
克隆PDB
从一个容器现有PDB克隆新的PDB	语法： CREATE PLUGGABLE DATABASE FROM FILE_NAME_CONVERT=('', '<path');< span="" style="word-wrap: break-word;">   例子：
	ALTER PLUGGABLE DATABASE pdb1 CLOSE; ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;   CREATE OR REPLACE VIEW pdb_status AS SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;   SELECT * FROM pdb_status; NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED                       READ ONLY  NO         NORMAL PDB1                        READ ONLY  NO         NORMAL   CREATE PLUGGABLE DATABASE pdb1_t FROM pdb1FILE_NAME_CONVERT = ('\uwpdb\', '\uwpdbd\'); SELECT * FROM pdb_status; NAME                           OPEN_MODE  RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED                       READ ONLY  NO         NORMAL PDB1                        READ ONLY  NO         NORMAL
创建时的UNPLUG/PLUGPDB
Unplug 子句
Unplug 子句   从unplugged创建	语法： ALTER PLUGGABLE DATABASE UNPLUG INTO '';   例子：
	conn / as sysdba ALTER PLUGGABLE DATABASE pdb1 CLOSE; ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.xml'; -- 将文件ftp到新服务器 -- the xml 文件在 $ORACLE_HOME/database下   SQL> CREATE PLUGGABLE DATABASE pdb2 USING 'pdb1.xml'  SOURCE_FILE_NAME_CONVERT = ('\pdb1\', '\pdb2\')  NOCOPY  STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 20M)  TEMPFILE REUSE;
更改PDB
设置子句
默认版本	语法： ALTER PLUGGABLE DATABASE   例子：
	conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; CREATE EDITION test_ed; ALTER PLUGGABLE DATABASE pdb1 DEFAULT EDITION = test_ed;
默认表空间	语法： ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE ;   例子：
	conn / as sysdba   ALTER SESSION SET CONTAINER = pdb1; SELECT tablespace_nameFROM dba_tablespaces; ALTER PLUGGABLE DATABASE pdb1 DEFAULT TABLESPACE oradata;
默认表空间类型	ALTER PLUGGABLE DATABASE SET DEFAULT TABLESPACE;
	conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 SET DEFAULT BIGFILE TABLESPACE;
默认临时表空间	ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE ;;
	conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 DEFAULT TEMPORARY TABLESPACEtemp_grp;
重命名	语法： ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO [.domain];   例子：
	conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; SELECT *FROM global_name;   ALTER PLUGGABLE DATABASE pdb1 RENAME GLOBAL_NAME TO pdb$dev;
PDB内设置时区	语法： ALTER PLUGGABLE DATABASE [] SET TIME_ZONE = ''; ALTER PLUGGABLE DATABASE SET TIME_ZONE = '< | -> HH:MI';   例子：
	conn sys@pdb1 as sysdba ALTER SESSION SET CONTAINER = pdb1; SELECT value$ FROM props$ WHERE name = 'DBTIMEZONE'; ALTER PLUGGABLE DATABASE pdb1 SET TIME_ZONE = 'Japan'; SELECT value$FROM props$WHERE name = 'DBTIMEZONE'; ALTER PLUGGABLE DATABASE pdb1 SET TIME_ZONE = '00:00'; SELECT value$FROM props$WHERE name = 'DBTIMEZONE';
数据文件重命名	语法： ALTER PLUGGABLE DATABASE RENAME FILE '' TO '';
	例子： conn sys@pdb1 as sysdba   ALTER PLUGGABLE DATABASE CLOSE; ​ ​ALTER PLUGGABLE DATABASE pdb1 RENAME FILE 'C:\APP\ORACLE\ORADATA\PDB1\PDB11.DBF' TO 'C:\APP\ORACLE\ORADATA\PDB1\PDB101.DBF'; --原数据文件需手工去除
创建数据文件	ALTER PLUGGABLE DATABASE CREATE DATAFILE <'' | filenumber> [AS | NEW]
更改数据文件	ALTER PLUGGABLE DATABASE DATAFILE <'' | filenumber> <online |="" offline="" [for="" drop]="" resize="" | | END BACKUP>;
更改临时文件	ALTER PLUGGABLE DATABASE TEMPFILE <'' | filenumber> <resize |="" | DROP [INCLUDING DATAFILES] | ONLINE | OFFLINE>;
移除数据文件	ALTER PLUGGABLE DATABASE MOVE DATAFILE <'' | ASM_file_name | filenumber>TO '' [REUSE] [KEEP]
补充日志	语法： ALTER PLUGGABLE DATABASE SUPPLEMENTAL LOG <data |="" | > 例子：
	ALTER PLUGGABLE DATABASE pdb1 ADD SUPPLEMENTAL LOG DATA;
存储参数	语法： ALTER PLUGGABLE DATABASE STORAGE <unlimited |="" >; 例子：
	ALTER PLUGGABLE DATABASE pdb1 STORAGE UNLIMITED;
	例子： ALTER PLUGGABLE DATABASE STORAGE (MAX_SHARED_TEMP_SIZE <unlimited |="" ); 例子：
	ALTER PLUGGABLE DATABASE pdb1 STORAGE (MAX_SHARED_TEMP_SIZE UNLIMITED);
Datafile子句
数据文件联机/脱机	语法： ALTER PLUGGABLE DATABASE DATAFILE ALL ;
	例子： conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 CLOSE; SELECT open_modeFROM v$pdbs; SELECT file#, status, enabledFROM v$datafile; ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL OFFLINE;   ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL ONLINE; SELECT file#, status, enabledFROM v$datafile;   ALTER PLUGGABLE DATABASE pdb1 OPEN;
Recovery子句
备份和恢复PDB	语法： ALTER PLUGGABLE DATABASE RECOVER [AUTOMATIC] [FROM ''  DATABASE;例子：
	ALTER PLUGGABLE DATABASE pdb1 RECOVER AUTOMATIC DATABASE;
	语法： ALTER PLUGGABLE DATABASE BACKUP; 例子：
	conn / as sysdba SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ALTER PLUGGABLE DATABASE pdb1 OPEN; ALTER SESSION SET CONTAINER = PDB1; ALTER PLUGGABLE DATABASE pdb1 BEGIN BACKUP; ALTER PLUGGABLE DATABASE pdb1 END BACKUP;
Change 子句
读写模式打开	语法： ALTER PLUGGABLE DATABASE OPEN READ [WRITE] [UPGRADE] [RESTRICTED] [FORCE][INSTANCES = <('' | )] >); 例子：
	ALTER PLUGGABLE DATABASE pdb1 OPEN;
只读模式打开	语法： ALTER PLUGGABLE DATABASE OPEN READ ONLY ; 例子：
	ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
Resetlogs模式打开	语法： ALTER PLUGGABLE DATABASE OPEN RESETLOGS ; 例子：
	ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
打开/关闭PDB	语法： ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
	例子： ALTER PLUGGABLE DATABASE pdb1 CLOSE;
打开/关闭所有PDB	语法： ALTER PLUGGABLE DATABASE ALL CLOSE [IMMEDIATE];
	例子： ALTER PLUGGABLE DATABASE ALL CLOSE;
只读模式打开PDB	语法： ALTER PLUGGABLE DATABASE READ ONLY; 例子：
	conn / as sysdba ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
以READ ONLY RESTRICTED模式打开PDB	语法： ALTER PLUGGABLE DATABASE OPEN READ ONLY RESTRICTED; 例子：
	conn / as sysdbaALTER PLUGGABLE DATABASE pdb1 CLOSE;ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY RESTRICTED;
移除PDB
从容器中删除PDB	语法： DROP PLUGGABLE DATABASE [INCLUDING DATAFILES]; 例子：
	ALTER PLUGGABLE DATABASE pdb1 CLOSE;DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;