How to Create a New Temporary Tablespace

I tested this on Oracle 9. You can see a database’s default temporary tablespace as follows:

SQL> select property_value

  2  from database_properties

  3  where property_name = 'DEFAULT_TEMP_TABLESPACE'

  4  /

PROPERTY_VALUE

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

TEMPORARY_DATA

SQL>

In this database, all the users have this as their temporary tablespace:

SQL> select temporary_tablespace, count(*)

  2  from dba_users

  3  group by temporary_tablespace

  4  /

TEMPORARY_TABLESPACE             COUNT(*)

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

TEMPORARY_DATA                       1304

SQL>

You cannot drop a database’s default temporary tablespace:

SQL> drop tablespace temporary_data

  2  /

drop tablespace temporary_data

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

SQL>

So, if you want to do this, you need to create another temporary tablespace:

SQL> create temporary tablespace newtemp

  2  tempfile '/usr/users/oracle/andrew/newtemp.dbf'

  3  size 100m

  4  /

Tablespace created.

SQL>

... and make this the database’s default temporary tablespace:

SQL> alter database

  2  default temporary tablespace newtemp

  3  /

Database altered.

SQL>

Oracle reassigns users to this new temporary tablespace for you:

SQL> select temporary_tablespace, count(*)

  2  from dba_users

  3  group by temporary_tablespace

  4  /

TEMPORARY_TABLESPACE             COUNT(*)

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

NEWTEMP                              1304

SQL>

But, if somebody is still using the old tablespace, you will not be able to drop it (the command below will hang):

SQL> l

  1* select username, tablespace, blocks from v$sort_usage

SQL> /

USERNAME   TABLESPACE      BLOCKS

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

ORACLE     TEMPORARY_DATA    3776

SQL> drop tablespace temporary_data

  2  /

drop tablespace temporary_data

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL>

Once they have finished, you will then be able to drop the old tablespace. Asking them to end their current SQL statement may not release the space – you may need to get them to logout too:

SQL> l

  1* select username, tablespace, blocks from v$sort_usage

SQL> /

no rows selected

SQL> drop tablespace temporary_data

  2  /

Tablespace dropped.

SQL>