Changing default temp tablespace

[Brian_274]

Hi all,
I'm use to working with 7 or 9 so forgive this stupid question. I know in oracle 9 I'm able to change the default temp tablespace with the following command:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

But in oracle 8 this doesn't work. I don't see anything in the online documentation regarding this. Am I correct in assuming that if the db was created with the default temporary tablespace as system it can't be changed?

Brian.

[A. Clay Stephenson]

The DEFAULT TEMPORARY TABLESPACE tablesp syntax falls under ALTER USER username rather than ALTER DATABASE.

[Brian_274]

I'm not talking about setting an individual users temporary tablespace. I'm talking about change the default temporary tablespace parameter. So if I create a new user and don't specify the temporary tablespace, oracle assigns it to that temporary tablespace. Currently our oracle 8 database assigns it to SYSTEM.

[Jean-Luc Oudart]

Brian,

when you create a new user you should specify default tablespace and temporary tablespace for the user.

Don't rely on the default !

Regards
Jean-Luc

[Brian Crabtree · Accepted solution]

Brian,

The DEFAULT TEMPORARY TABLESPACE is specified in the initial creation of the database. This funcationality was added in Oracle 9i. All it does is allows you to specify the default temporary tablespace for users on creation. It is a good idea to set, since Oracle will create a user without the temporary tablespace set specificly in the SYSTEM tablespace.

This command was given, because you cannot drop a temporary tablespace set to default. This command can only be used to set a default temporary tablespace (new 9i funcationilty). This should not be confused with a permamant tablespace set to TEMPORARY.

Thanks,

Brian

[Indira Aramandla]

Hi Brian,

In Oracle9i, the database can be assigned a DEFAULT TEMPORARY TABLESPACE. This feature is designed for database users to have a default temporary tablespace automatically assigned other than the SYSTEM tablespace.

But for all the other releases before 9i, the temporary tablespace has to be defined for each user otherwise it takes the system tablespace as the temporary tablespace by default.


Indira

[twang]

You should always specify the temporary tablespace clause,
CREATE USER user IDENTIFIED { BY password | INTERNALLY }
{
| DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE tablespace
| { QOUTA { integer [ K | M ] | UNLIMITED } ON tablespace } <+loop>
| PROFILE profile
} <+loop>

or you can follow an alter statement after create statement,
ALTER USER user TEMPORARY TABLESPACE

[Yogeeraj_1]

hi brian,

as IA said above, in Oracle9i, the database can be assigned a DEFAULT TEMPORARY TABLESPACE.

see metalink note 138212.1.

regards
Yogeeraj

[Sanjay Kumar Suri]

Hello Brian

The default_settings_clauses of alter database were introduced in 9i.

CHARACTER SET character_set
NATIONAL CHARACTER SET character_set
set_time_zone_clause
DEFAULT TEMPORARY TABLESPACE tablespace
RENAME GLOBAL_NAME TO database . domain [. domain]...

The new (9i) DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement allows you to create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace with create/alter SQL.

sks

[Brian_274]

Ok so it's 9i functionality. Thanks guys. At my old company I've always created temp as the default temporary tablespace at creation time so I never came across this before. Someone set all the ones here to system.