Operating System - HP-UX
1748266 Members
3520 Online
108760 Solutions
New Discussion юеВ

Re: Changing default temp tablespace

 
SOLVED
Go to solution
Brian_274
Frequent Advisor

Changing default temp tablespace

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.
9 REPLIES 9
A. Clay Stephenson
Acclaimed Contributor

Re: Changing default temp tablespace

The DEFAULT TEMPORARY TABLESPACE tablesp syntax falls under ALTER USER username rather than ALTER DATABASE.
If it ain't broke, I can fix that.
Brian_274
Frequent Advisor

Re: Changing default temp tablespace

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
Honored Contributor

Re: Changing default temp tablespace

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
fiat lux
Brian Crabtree
Honored Contributor
Solution

Re: Changing default temp tablespace

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
Honored Contributor

Re: Changing default temp tablespace

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

Never give up, Keep Trying
twang
Honored Contributor

Re: Changing default temp tablespace

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
Honored Contributor

Re: Changing default temp tablespace

hi brian,

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

see metalink note 138212.1.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: Changing default temp tablespace

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

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Brian_274
Frequent Advisor

Re: Changing default temp tablespace

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.