2008/11/10

Relocate tempfile in 10g

database 10.1.0.4

You cannot use "ALTER DATABASE RENAME FILE ... TO ..." to relocate a tempfile in 10g.

If the temporary tablespace to which tempfile belongs is the default tablespace, then
1) Copy the tempfile to the new location using OS command.
2) Create new temporary tablespace
CREATEA TEMPORARY TABLESPACE new_name TEMPFILE 'new_tempfile_name' REUSE;
3) Make it the default temp tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'new_name';
4) Drop the old tablespace
DROP TABLESPACE 'old_name' including contents and datafiles;

If you want to keep the same temporary tablespace name, then rename the old name first using:

SQL> ALTER TABLESPACE 'old_name' RENAME TO 'new_name';

No comments: