A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Undo Tablespace

2 min read
a red xbox game controller and an uno card

Photo by Utkarsh Modgil on Pexels.com

There are two types of Undo Modes in 12.2

1) Shared Undo - The Undo tablespace is common, it is shared among the entire CDB. The Management of undo tablespace is similar to Non-CDB database.

2) Local Undo - The Local undo is local particular PDB or a container
The benefits having local undo enables below operations on PDB.

1) Relocate PDB
2) Plugging PDB
3) Flashback PDB
4) PITR of PDB without auxiliary Instance

How to check the Undo mode for the database

SQL> set lines 300
SQL> col property_name for a30
SQL> col description for a40
SQL> col PROPERTY_VALUE for a15
SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
 
PROPERTY_NAME                 PROPERTY_VALUE       DESCRIPTION
------------------------------    ---------------             ----------------------------------------
LOCAL_UNDO_ENABLED       TRUE                          true if local undo is enabled

Changing UNDO mode to Local Undo

SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
 
no rows selected
 
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter database local undo on;
SQL> shutdown immediate
SQL> startup
 
SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
 
PROPERTY_NAME                  PROPERTY_VALUE      DESCRIPTION
------------------------------    ---------------             ----------------------------------------
LOCAL_UNDO_ENABLED             TRUE                    true if local undo is enabled

Turn off Local Undo

SQL> shutdown immediate
SQL> startup upgrade
SQL> alter database local undo off;
Drop undo tablespace in PDB
SQL> shutdown immediate
SQL> startup

EDIT PDB$SEED to change Undo properties

The Database must be in Local Undo Mode.

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
SQL> CREATE UNDO TABLESPACE LOCALUNDO DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;

NOTE: The PDB$SEED in shared undo mode can be open to read write with “_oracle_srcipt”=TRUE  underscore parameter.