Another post that’s unlikely to be all that useful….but you never know.

As per a couple of my previous posts, I’m re-doing my Oracle certification from the ground up instead of doing the 10G new features only.

I’m using Oracle Database 10g OCP Certification by Damir Bersinic and John Watson, which from the bits I’ve read so far, I would recommend.

Anyhow, the book says:

If you are using UNDO_MANAGEMENT=AUTO, you must also specify UNDO_TABLESPACE.

I was curious to see how this was enforced, in particular whether you would get an error if you tried to ALTER SYSTEM to nullify the undo tablespace parameter. You don’t - you get an error when you try to use the undo, as follows:

SQL> @sparam
Enter value for parameter_name: UNDO
old   5: where name like lower('%&parameter_name%')
new   5: where name like lower('%UNDO%')
undo_management                AUTO                                          F
undo_retention                 900                                           T
undo_tablespace                UNDOTBS1                                      F

SQL> alter system set undo_tablespace='' scope =both;

System altered.

SQL> delete from scott.emp;
delete from scott.emp
                  *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'