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.
Explanation:
Interesting behavior in 12.1.0.1 DB of creating an undo tablespace in a PDB. With
the new Multitenant architecture the undo tablespace resides at the CDB level and PDBs all share
the same UNDO tablespace.
When the current container is a PDB, an attempt to create an undo tablespace fails without
returning an error.
You’re right (E) . Very strange. see :
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm
E – TRUE
When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.
E
E
E
https://oracledb101.wordpress.com/2013/07/22/undo-that-is-silently-undone/
http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm
“When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.”
this question is very tricky, the correct answer would be “It fails and does not report an error” not “It executes successfully but …”, anyway E
E
Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace. Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.
On PDB
SQL> show con_name;
CON_NAME
——————————
PDBAR01
SQL> create undo tablespace undotest datafile ‘/data02/oradata/PERSO/db01/CONDBT1/PDBAR01/undotest01.dbf’ SIZE 60M AUTOEXTEND ON;
Tablespace created.
SQL> !
tail -f alert_CONDBT1.log
create undo tablespace undotest datafile ‘/data02/oradata/PERSO/db01/CONDBT1/PDBAR01/undotest01.dbf’ SIZE 60M AUTOEXTEND ON
Completed: create undo tablespace undotest datafile ‘/data02/oradata/PERSO/db01/CONDBT1/PDBAR01/undotest01.dbf’ SIZE 60M AUTOEXTEND ON
On CDB
select tablespace_name from cdb_tablespaces where tablespace_name like ‘UNDO%’
UNDOTBS1
on PDB
SQL> show con_name;
CON_NAME
——————————
PDBAR01
SQL> select tablespace_name from cdb_tablespaces where tablespace_name like ‘UNDO%’
2 ;
no rows selected
Shell> ls -lrt /data02/oradata/PERSO/db01/CONDBT1/PDBAR01/
total 1365904
-rw-r—–. 1 oracle oinstall 91234304 Mar 3 13:46 PDBAR01_temp01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Mar 3 15:19 SAMPLE_SCHEMA_users01.dbf
-rw-r—–. 1 oracle oinstall 375529472 Mar 3 15:19 example01.dbf
-rw-r—–. 1 oracle oinstall 283123712 Mar 3 15:45 system01.dbf
-rw-r—–. 1 oracle oinstall 734011392 Mar 3 15:45 sysaux01.dbf
So Answer is Definitely E — No Errors/No Warnings, No datafiles, No tablespace
E this correct:
SQL> CREATE UNDO TABLESPACE undotb01 DATAFILE ‘/u01/app/oracle/db12cm/undotbs01.dbf’ SIZE 60M AUTOEXTEND ON;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
TEMP
USERS
SQL> select name from dba_datafiles;
select name from dba_datafiles
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> !ls -lrth /u01/app/oracle/db12cm/undotbs01.dbf
ls: cannot access /u01/app/oracle/db12cm/undotbs01.dbf: No such file or directory
SQL>