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>

More About Bigfile Tablespaces

You can read an introduction to bigfile tablespaces here.

This was tested in an Oracle 10 database. In DATABASE_PROPERTIES, there is a row which determines whether new tablespaces will be SMALLFILE or BIGFILE by default. When you first create a database, this is set to SMALLFILE: 

SQL> l

  1  select property_value, description

  2  from database_properties

  3* where property_name = 'DEFAULT_TBS_TYPE'

SQL> /

 

PROPERTY_VALUE  DESCRIPTION

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

SMALLFILE       Default tablespace type

 

SQL>

 

You can change this as follows, if you wish:

 

SQL> alter database set default bigfile tablespace

  2  /

 

Database altered.

 

SQL> select property_value, description

  2  from database_properties

  3  where property_name = 'DEFAULT_TBS_TYPE'

  4  /

 

PROPERTY_VALUE  DESCRIPTION

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

BIGFILE         Default tablespace type

 

SQL>

 

Then, if you create a new tablespace, it will be BIGFILE by default:

 

SQL> create tablespace andrew

  2  datafile '/tmp/andrew.dbf' size 10m

  3  /

 

Tablespace created.

 

SQL> select bigfile from dba_tablespaces

  2  where tablespace_name = 'ANDREW'

  3  /

 

BIGFILE

-------

YES

 

SQL>

 

... and, if you want to create a SMALLFILE tablespace, you have to add the SMALLFILE keyword to your CREATE TABLESPACE command:

 

SQL> create smallfile tablespace fred

  2  datafile '/tmp/fred.dbf' size 10m

  3  /

 

Tablespace created.

 

SQL> select bigfile from dba_tablespaces

  2  where tablespace_name = 'FRED'

  3  /

 

BIGFILE

-------

NO

 

SQL>