How to Switch/Recreate Undo Tablespace at PDB Level (Doc ID 2560841.1)

 

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Goal

How to Switch/Recreate Undo Tablespace at PDB level
-------------------------------------------------------------
SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';  

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE
 
show parameter undo;

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

Solution

$ sqlplus / as sysdba;

SQL> startup
     ORACLE instance started.

SQL> show pdbs

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

SQL> alter session set container=PDB1;

SQL> alter pluggable database PDB1 open restricted;

SQL> show pdbs

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

SQL> CREATE UNDO TABLESPACE test_undo datafile '...../PDB1/test_undo.dbf'  SIZE 100M AUTOEXTEND ON ;

SQL> ALTER SYSTEM set  UNDO_TABLESPACE='TEST_UNDO' scope=both;

SQL> DROP tablespace UNDOTBS1 including contents and datafiles;  << Drop previous undo tablespace

SQL> alter pluggable database PDB1 close;
SQL> alter pluggable database PDB1 open;
 
Also Check  : Undo Modes in 12.2 Multitenant Databases - Local and Shared Modes (Doc ID 2169828.1)