Oracle Database

An UNDO in a PDB in Oracle 12c?

1 min read

Feb 4, 2016 12:00:00 AM

According to the Oracle 12cR1 documentation and concepts, it is 100% clear that there can be only one UNDO tablespace in a multitenant architecture and it is at CDB level; thus, a PDB cannot have any UNDO tablespace. Are we really sure about that? Let's test it!

Testing UNDO Tablespace Creation in a PDB

First, we need a PDB with few tablespaces:

FRED_PDB> select NAME, OPEN_MODE, CON_ID from v$pdbs ;   NAME OPEN_MODE CON_ID  -------------------------------------------------- ---------- ----------  FRED_PDB READ WRITE 4   FRED_PDB> select tablespace_name from dba_tablespaces ;   TABLESPACE_NAME  ------------------------------  SYSTEM  SYSAUX  TEMP  USERS  TBS_DATA  5 rows selected.   FRED_PDB> show parameter undo   NAME TYPE VALUE  ------------------------------------ ----------- ------------------------------  temp_undo_enabled boolean FALSE  undo_management string AUTO  undo_retention integer 900  undo_tablespace string UNDOTBS1  FRED_PDB>

There we have an UNDO tablespace named UNDOTBS1 at CDB level and no UNDO at PDB level. Let's try to create one

FRED_CDB> create undo tablespace MY_PDB_UNDO ;   Tablespace created.  FRED_CDB>

Verifying the "Successful" Creation

It worked! Is the Oracle documentation wrong? Let's verify this weird successful UNDO tablespace creation:

FRED_PDB> select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%' ;   no rows selected   FRED_PDB> select tablespace_name from dba_tablespaces   TABLESPACE_NAME  ------------------------------  SYSTEM  SYSAUX  TEMP  USERS  TBS_DATA  5 rows selected.  FRED_PDB>

Documentation vs. Reality: Feature or Bug?

No UNDO tablespace has in fact been created even if no error message has been raised by Oracle. Digging in the documentation, this is not a not a bug but a feature. Indeed, it is well specified that:

When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.

Please note that this is the behavior of the 12cR1 release; from my side, I think that this a "not yet feature" and we should see some real UNDO tablespaces in PDBs in the next release(s)!