Which tablespaces are mandatory in an Oracle database for it to be operational? (Choose all that
apply.)
A.
Undo tablespace
B.
USERS tablespace
C.
SYSAUX tablespace
D.
SYSTEM tablespace
E.
Temporary tablespace
Which tablespaces are mandatory in an Oracle database for it to be operational?
Which tablespaces are mandatory in an Oracle database for it to be operational? (Choose all that
apply.)
Hello, You can take the sysaux tablespace offline, the operational keyword is a bit dicey, probably this question Oracle does not rates as part of the exam.
However, taking an Undo tablespace offline will not be allowed and it is obvious, any dml operation will generate UNDO data.
Also, similarly default Temporary Tablespace of DB needs to be there.
Let me know your views experts 🙂
Cheers.
Vineet
from documentation:
•An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database’s data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
http://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm#i10698
Tablespace UNDO, not important for transaction?
If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment.
So undo and temp tablespace are optional in these cases.
Of course UNDO is crucial for an Oracle DB, but you can use rollback segments (deferred) instead… That’s why undo tablespace is not MANDATORY. getting rid of UNDO data is mandatory though!
When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.