Tuesday, June 12, 2012

How to Create a New Temporary Tablespace



This post corrects a small mistake in How to Recreate a Temporary Tablespace

It was tested 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.
(I did not realise this when I wrote How to Recreate a Temporary Tablespace):

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>

No comments: