在PDB级别中如何切换或重建UNDO表空间

jiawang

Oracle 12.2版本开始，UNDO表空间同时可以存在每个PDB级别（本地UNDO）。
MOS 文档 ID 2560841.1 使用版本：Oracle Database - Enterprise Edition - Version 12.2.0.1 及之后版本
目录
1. 确认是否开启本地UNDO
2. 关闭当前PDB并以受限模式打开
3. 创建新的UNDO表空间并切换
4. 删除旧的UNDO表空间并重启

1. 确认是否开启本地UNDO

SQL> select property_name, property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME           PROPERTY_VALUE
----------------------- ---------------
LOCAL_UNDO_ENABLED      TRUE


2. 关闭当前PDB并以受限模式打开

SQL> show pdbs

CON_ID     CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
2          PDB$SEED   READ ONLY  NO
3          PDB1       READ WRITE NO

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open restricted;

Pluggable database altered.


3. 创建新的UNDO表空间并切换

SQL> alter session set container=pdb1;

Session altered

SQL> show pdbs

CON_ID     CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
3          PDB1       READ WRITE YES

SQL> show parameter undo

NAME                 TYPE         VALUE
-------------------- ------------ --------
temp_undo_enabled    boolean      FALSE
undo_management      string       AUTO
undo_retention       integer      900
undo_tablespace      string       UNDOTBS1

SQL> select name from v$datafile;

NAME
-----------------------------------------------
/u01/app/oracle/oradata/CDB2/PDB1/system01.dbf
/u01/app/oracle/oradata/CDB2/PDB1/sysaux01.dbf
/u01/app/oracle/oradata/CDB2/PDB1/undotbs01.dbf
/u01/app/oracle/oradata/CDB2/PDB1/users01.dbf

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/CDB2/PDB1/undotbs201.dbf' size 50m autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo_tablespace

NAME              TYPE    VALUE
----------------- ------- --------
undo_tablespace   string  UNDOTBS2


4. 删除旧的UNDO表空间并重启

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
     3 PDB1           READ WRITE NO