RE: Default Temporary Tablespace
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <wjwagman@xxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Jan 2009 17:53:21 -0500
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.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of William Wagman
Sent: Monday, January 05, 2009 5:10 PM
To: oracle-l
Subject: Default Temporary Tablespace
Greetings,
I'm running Oracle 10.2.0.4.0EE 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?
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
