RE: Default Temporary Tablespace

From: Mark W. Farnham <>
Date: Mon, 5 Jan 2009 17:53:21 -0500
Message-ID: <>

That setting affects what the temporary tablespace is for a newly created user. The only time where there might be a theoretical catch-22 is at database creation time in the phase where only system exists. But I'm not aware of a need for anything needing to actually sort anything at that phase, so I do not believe you can get yourself into a box canyon. There should not be a need to create new users when the only tablespace you can open is system, so I'm pretty sure you're in the clear.

If someone has a case that might be problematic, I'm all ears.

Still, it seems pretty cheeky of Oracle to INSIST that the default temporary tablespace is of TEMPORARY type when the database is not created that way!

In fact I believe the resource should not have to exist yet to have it be designated the temporary tablespace. Errors of that sort are best left for detection at run time and in the case of a temporary tablespace until you actually try to use temporary segments. The pre-emptive check can certainly be frustrated between being set and a user being created, so what benefit there is to making it an error instead of only a warning or advisory message is unclear to me.



-----Original Message-----

From: [] On Behalf Of William Wagman
Sent: Monday, January 05, 2009 5:10 PM
To: oracle-l
Subject: Default Temporary Tablespace


I'm running Oracle on RHEL4. Following Metalink note 273276.1 I resized the temporary tablespace in a test database. However, I was not paying attention and issued the command

SQL> alter database default temporary tablespace <new_temp_tablespace>;

although the temporary tablespace I was resizing was not the default temporary tablespace, the system tablespace was. Upon attempting to again make the system tablespace the default temporary tablespace I receive an ORA-12901 error...

SQL> alter database default temporary tablespace system; alter database default temporary tablespace system; *
ERROR at line 1:
ORA-12901: default temporary tablespace must be of TEMPORARY type

My question is are there potential problems with the system tablespace *not* being the default temporary tablespace? If so what are the potential problems and is there a way to restore the system tablespace as the default temporary tablespace short of rebuilding the database?


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208


-- Received on Mon Jan 05 2009 - 16:53:21 CST

Original text of this message