topic Changing default temp tablespace in Operating System - HP-UX

Changing default temp tablespace

Brian_274 — Tue, 17 Feb 2004 11:08:31 GMT

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.

Re: Changing default temp tablespace

A. Clay Stephenson — Tue, 17 Feb 2004 11:15:20 GMT

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

Re: Changing default temp tablespace

Brian_274 — Tue, 17 Feb 2004 11:22:12 GMT

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.

Re: Changing default temp tablespace

Jean-Luc Oudart — Tue, 17 Feb 2004 12:40:22 GMT

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

Re: Changing default temp tablespace

Brian Crabtree — Tue, 17 Feb 2004 17:37:01 GMT

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

Re: Changing default temp tablespace

Indira Aramandla — Tue, 17 Feb 2004 19:42:35 GMT

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

Re: Changing default temp tablespace

twang — Tue, 17 Feb 2004 20:10:26 GMT

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

Re: Changing default temp tablespace

Yogeeraj_1 — Tue, 17 Feb 2004 20:25:18 GMT

hi brian,

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

see metalink note 138212.1.

regards
Yogeeraj

Re: Changing default temp tablespace

Sanjay Kumar Suri — Wed, 18 Feb 2004 00:45:26 GMT

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

Re: Changing default temp tablespace

Brian_274 — Wed, 18 Feb 2004 09:38:51 GMT

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.