What are the mandatory/required oracle 11g tablespaces?

Question

Ok, what are the mandatory tablespaces in oracle 11g. My research has lead me to believe there are three of them: System, Sysaux, and Undo. However since I am taking the OCA and all the questions(and articles) I have read about mandatory spaces and answered only say two spaces are mandatory, and depending on where question came from the answer is different.

System is mandatory. That is a fact and undisputed.

The Sysaux and Undo tablespaces are the ones in question. Which one? Again my research has lead me to believe all three are, but oracles documentation is unhelpful at this point(or maybe I am using the wrong keywords in search?) and the practice questions lead me to believe that the test is looking for two tablespaces.

Answer 1

You will have to examine how the question is worded. By default 11g will create all three tablespaces. You can however create a database with only SYSTEM and SYSAUX. Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.

From the 11.2 Administration Guide:

The SYSAUX tablespace is always created at database creation.

From another section of the Administration Guide:

An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA)...

If no undo tablespace is available, then the instance starts without an undo tablespace, and undo data is written to the SYSTEM tablespace. You should avoid running in this mode.

From the 10.2 Concepts Guide:

...the SYSAUX tablespace is always created during database creation or database upgrade...

During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

Note: If the SYSAUX tablespace is unavailable, such as due to a media failure, then some database features might fail.

From the 10.1 SQL Reference:

You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode.

From the 10.1 Administrators Guide:

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:

The SYSAUX tablespace is a new tablespace that is required in Oracle Database 10g...

When you migrate to Oracle Database 10g, the SYSAUX tablespace needs to be created as a part of that migration. This is done after mounting the database under the new Oracle Database 10g database software. Once you have mounted it, open the database in migrate mode with the startup migrate command. After the database is open, you can create the SYSAUX tablespace.

Note: The loss of the SYSAUX tablespace is not fatal to your database. In our testing it appears that the only real impact is that certain functionality related to the occupants of the SYSAUX tablespace is lost.

Earlier versions did not have a SYSAUX tablespace:

SYSAUX is the name of the compulsory tablespace, introduced in Oracle 10g.

Answer 2

My recollection is that only the SYSTEM tablespace is required. The SYS user can (and does) use old-school rollback segments in the SYSTEM tablespace.

Answer 3

If the database is already created and running then you need SYSTEM and Undo tablespace. The rest you can drop and database is still running. However is it true that you can create database with only SYSTEM wich is used as Undo, Temporary. And it is true that by default during installation of database SYSTEM and SYSAUX are created.