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! First, we need a PDB with few tablespaces: [code]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>[/code] There we have an UNDO tablespace named UNDOTBS1 at CDB level and no UNDO at PDB level. Let's try to create one : [code]FRED_CDB> create undo tablespace MY_PDB_UNDO ; Tablespace created. FRED_CDB>[/code] It worked! Is the Oracle documentation wrong? Let's verify this weird successful UNDO tablespace creation: [code]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>[/code] 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: [code]When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.[/code] 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)! Discover more about our expertise in Oracle.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

An UNDO in a PDB in Oracle 12c?

Share this

Share this

More resources

Making existing SQLPLUS Scripts 12c and Container DB (PDB) compatible

Interesting variations in outcome when cloning a CDB with a subset of PDBs in Oracle

12c: How to Restore/Recover a Small Table in a Large Database

Ready to unlock value from your data?