PDB的基本管理

1. 查看当前数据的状态和信息

SQL> select name,decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;NAME      Multitenant Option         OPEN_MODE                CON_ID--------- -------------------------- -------------------- ----------ZYLONG    Multitenant Option enabled READ WRITE                    0

2. 查看PDB信息

SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 SEED_PDB                       READ WRITE NO         4 NONCDB_PDP                     READ WRITE NO         5 CLON_PDB                       READ WRITE NO

3. 切换到PDB数据库

SQL> alter session set container=seed_pdb;Session altered.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         3 SEED_PDB                       READ WRITE NO

4. 关闭和启动PDB

##第一种方式

SQL> conn / as sysdbaConnected.SQL> alter pluggable database clon_pdb close;Pluggable database altered.SQL> show pdbs;        CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 SEED_PDB                       READ WRITE NO         4 NONCDB_PDP                     READ WRITE NO         5 CLON_PDB                       MOUNTEDSQL> alter pluggable database clon_pdb open;Pluggable database altered.SQL>  show pdbs;     CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 SEED_PDB                       READ WRITE NO         4 NONCDB_PDP                     READ WRITE NO         5 CLON_PDB                       READ WRITE NO

##第二种方式

SQL> alter session set container=CLON_PDB;Session altered.SQL> shutdown immediatePluggable Database closed.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         5 CLON_PDB                       MOUNTEDSQL> startup;Pluggable Database opened.SQL> show pdbs;    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         5 CLON_PDB                       READ WRITE NO

5. 删除PDB，添加including datafiles选项，数据文件也会删除

SQL> alter pluggable database clon_pdb close;SQL> drop pluggable database clon_pdb including datafiles;

6. 当CDB重启,保存PDB之前的打开状态

12c中一个CDB中可以有多个PDB，当重启了CDB，默认情况下PDB是不会打开的。

6.1 12CR1通过触发器

##CDB下创建一个触发器,打开所有PDB

CREATE TRIGGER open_all_pdbs   AFTER STARTUP   ON DATABASEBEGIN   EXECUTE IMMEDIATE 'alter pluggable database all open';END open_all_pdbs;/

6.2 12CR2通过新特性SAVE STATE

通过SAVE STATE保存CDB重启前PDB的状态。这样重启CDB之后，会自动打开PDB到之前的状态。

保存PDB1打开状态

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

取消PDB1保存打开状态

ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

保存所有PDB打开状态

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

保存几个PDB打开状态

ALTER PLUGGABLE DATABASE pdb1,pdb2,pdb3 SAVE STATE;

保存除了PDB salespdb之外所有PDB的打开状态

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb SAVE STATE;