Automatically Assigned Default TEMPORARY TABLESPACE other than the SYSTEM TABLESPACE

Database Version : Oracle 9.0.1,9.2.0.2

Tip Revision Date : 25-JAN-2003

Web : www.SamOraTech.com

Author : Sameer Wadhwa

In Oracle 9i, you do not need to assign temporary tablespace while creating a database user. The Temporary Tablespace is automatically assigned. The name of the temporary tablespace is determined by the DEFAULT_TEMP_TABLESPACE column from the data dictionary view DATABASE_PROPERTIES_VIEW.

For clear understanding , let us see the following example :-

In Oracle 8i and before,

SQL> create user test identified by test123 default TABLESPACE users;

User created.

SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM

DBA_USERS WHERE USERNAME='TEST';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-------- ------------------------------ ------------------------------

TEST USERS SYSTEM

Now in Oracle 9i,

SQL> create user test identified by test123 default TABLESPACE users;

User created.

SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM

DBA_USERS WHERE USERNAME='TEST';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-------- ------------------------------ ------------------------------

TEST USERS TEMP

Temporary Tablespace TEMP is automatically assigned to the user TEST.

To Determine the DEFAULT TEMPORARY TABLESPACE, login as SYS and Query the DATABASE_PROPERTIES

View as follows:-

SQL :-

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME ='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_V DESCRIPTION

------------------------------ ---------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

If you want to assign a NEW DEFAULT TEMPORARY TABLESPACE, other then TEMP. You have to

1) Create a New Temporary Tablespace of TYPE TEMPORARY.

2) ALTER the Database to point to the NEW TEMPORARY TABLESPACE.

3) Drop the OLD TEMPORARY TABLESPACE

Step 1:-

SQL>CREATE TABLESPACE NEWTEMP

DATAFILE 'E:\OR9I\ORADATA\OR9I\NEWTEMP01.DBF' SIZE 100K

TEMPORARY;

Tablespace created.

Step 2:-

(A New Syntax in 9i)

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE NEWTEMP;

Database altered.

Step 3:-

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

All users having a TEMPORARY TABLESPACE TEMP before now will change to NEWTEMP automatically.

Certain Restrictions:-

There are certain points which you have to keep in mind

Ø THE DEFAULT TEMPORARY TABLESPACE MUST BE OF TYPE TEMPORAY.

Ø DEFAULT TEMPORARY TABLESPACE CAN NOT BE DROPED UNTIL , YOU CREATE

ANOTHER ONE

Ø DEFAULT TEMPORARY TABLESPACE CAN NOT BE TAKEN OFFLINE.

Reference:

Oracle 9i Administrator Guide

Technical Website: - www.SamOraTech.com