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.
We take the following steps to rename a 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.