Skip to content

How Oracle Rename TempFile

  • by

Rename TempFile

Earlier, we have talked about how to rename a datafile, now how about tempfile? How to rename it? The procedure of renaming an Oracle tempfile is actually the same as the procedure of moving tempfile with a different file name.

Let's check what tablespace owns this tempfile in our case.

SQL> select t.name tablespace_name from v$tempfile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf');

TABLESPACE_NAME
------------------------------
SYSTMP

As you can see, we erroneously named the tempfile as TEMP02.dbf in the new tablespace SYSTMP, which conflicts with our uniform naming convention of tablespace. We should correct the name into SYSTMP02.dbf.

TEMP02.dbf --> SYSTMP02.dbf

We take the following steps to rename a tempfile.

  1. Offline TempFile
  2. Move TempFile
  3. Rename TempFile
  4. Online Tempfile

1. Offline TempFile

We should prevent any change from the tempfile by offlining it.

SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf' offline;

Database altered.

2. Move TempFile

We use OS command mv to rename the physical file.

[oracle@test ~]$ cd /oradata/ORCLCDB/ORCLPDB/
[oracle@test ORCLPDB]$ mv TEMP02.dbf SYSTMP02.dbf
[oracle@test ORCLPDB]$ ll SYSTMP02.dbf
-rw-r----- 1 oracle oinstall 10493952 Oct 10 04:22 /oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf

3. Rename TempFile

Tell the database where the new location is.

SQL> alter database rename file '/oradata/ORCLCDB/ORCLPDB/TEMP02.dbf' to '/oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf';

Database altered.

Please note that, there's no ALTER DATABASE RENAME TEMPFILE statement, only ALTER DATABASE RENAME FILE is valid for DBA to move database files, no matter what type of the file is.

That is to say, if you did ALTER DATABASE RENAME TEMPFILE, you see ORA-02231 error against using invalid RENAME TEMPFILE clause.

4. Online Tempfile

We should make the tempfile go back to work.

SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/SYSTMP02.dbf' online;

Database altered.

The new name is working now.

There're more advanced ways to move temp files, you may take a look.

Leave a Reply

Your email address will not be published. Required fields are marked *