Your multitenant container database (CDB) contains pluggable databases (PDBs), you are connected to the HR_PDB. You execute the following command:
SQL > CREATE UNDO TABLESPACE undotb01
DATAFILE ‘u01/oracle/rddb1/undotbs01.dbf’ SIZE 60M AUTOEXTEND ON; What is the result?
A. It executes successfully and creates an UNDO tablespace in HR_PDB.
B. It falls and reports an error because there can be only one undo tablespace in a CDB.
C. It fails and reports an error because the CONTAINER=ALL clause is not specified in the command.
D. It fails and reports an error because the CONTAINER=CURRENT clause is not specified in the command.
E. It executes successfully but neither tablespace nor the data file is created.
why not B ?
The correct answer is E and not B
https://blog.pythian.com/undo-pdb-oracle-12c/
Even though 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. the command will execute successfully without creating the datafile and tablespace as shown below
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>
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>
No UNDO tablespace has in fact been created even if no error message has been raised by Oracle.