Oracle 19C CDB和PDB常用命令详解

1.查看当前容器 [oracle@rac19c1 ~]$ sqlplus / as sysdba SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> select sys_context(USERENV,CON_NAME) conname from dual; CONNAME ------------------------------ CDB$ROOT

2.创建PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> create pluggable database pdb1 admin user test identified by test; Pluggable database created.

SQL> create pluggable database pdb2 from pdb1; Pluggable database created.

SQL> alter pluggable database all open instances=all; Pluggable database altered.

3.查看PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 5 PDB2 MOUNTED

SQL> colu name for a30 SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB2 MOUNTED

SQL> set linesize 300 SQL> select con_id, dbid, guid, name , open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ------------------------------ ---------- 2 239193540 ACC08D4BFF0170FFE0536F57A8C0EE7A PDB$SEED READ ONLY 3 657947767 ACC102F858A7A18AE0537057A8C0AE9A PDB1 READ WRITE 5 2810655217 ACDA193AFC919610E0536F57A8C09544 PDB2 MOUNTED

4.切换容器（CDB$ROOT或PDB） SQL> alter session set container=pdb1; Session altered.

SQL> show con_name CON_NAME ------------------------------ PDB1

SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE NO SQL> select con_id, dbid, guid, name , open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ------------------------------ ---------- 3 657947767 ACC102F858A7A18AE0537057A8C0AE9A PDB1 READ WRITE

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 5 PDB2 MOUNTED SQL>

5.开启PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter pluggable database pdb2 open instances=all; Pluggable database altered. 或 SQL> alter session set container=pdb2; Session altered.

SQL> startup; Pluggable Database opened.

SQL> alter pluggable database all open instacnes=all; Pluggable database altered.

6.关闭PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> alter pluggable database pdb1 close instances=all; Pluggable database altered. 或 SQL> alter session set container=pdb1; Session altered. SQL> shutdown immediate; Pluggable Database closed.

SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> alter pluggable database all close instances=all; Pluggable database altered.

7.设置自启PDB SQL> show con_name

SQL> create or replace trigger open_all_pdbs after startup on database begin execute immediate alter pluggable database all open instances=all; end; /

8.删除PDB SQL> show con_name CON_NAME ------------------------------ CDB$ROOT

SQL> alter pluggable database pdb1 close instances=all; Pluggable database altered.

SQL> drop pluggable database pdb1 including datafiles; Pluggable database dropped.

SQL> alter pluggable database pdb2 close immediate instances=all; Pluggable database altered.

SQL> drop pluggable database pdb2 including datafiles; Pluggable database dropped.