What's going on in Mesiols work day

Tuesday, December 2, 2008

Rename Oracle temporary tablespace

Today i run into the problem, that a customer has created a temporary file for temp tablespace called ".dbf".

Because it is not possible to rename the file i did the following steps to get a usefull filename:

First i created a new temporary tablespace called temp2

SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/u05/oradata/db_1/data/TEMP2_01.dbf'
SIZE 1024M;

After that i switched the system to the new temp tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Than i dropped the old temp tablespace

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

and recreated it:

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u05/oradata/db_1/data/TEMP_01.dbf'
SIZE 1024M;

and changed the database back to tablespace named TEMP

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

and deleted TEMP2

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

This all works online.

Followers