In Oracle 10g there is a New syntax in the database_file_clauses lets to rename tempfiles as well as datafiles.
The following procedure to be followed to rename a tempfile
#####################################
If the file is the default temporary tablespace then you need perform the below from step 1,
Else if you want to move a datafile of a normal temporary tablespace then perform the below from step 2.
Step 1: create another default temporary tablespace.
Step 2: Take the tablespace in offline position
Step 3: Use os command to move or copy the files.
Step 4: Then perform:
SQL> alter database rename file '<PATH>\<NAME>' to '<NEW_PATH>\<NEW_NAME>';
Step 5: Bring the tablespace online.
Step 6: change the default temporary tablespace back to the original.
EXAMPLE:
STEP 1:
------
SQL> CREATE TEMPORARY TABLESPACE NEW_TEMP tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWEMP.DBF' SIZE 10G;
SQL> alter database default temporary tablespace NEW_TEMP ;
Database altered.
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties
where PROPERTY_NAME like '%FAULT%';
PROPERTY_NAME PROPERTY_VALUE
-----------------------------------------------
DEFAULT_TEMP_TABLESPACE NEW_TEMP
STEP 2:
-----
SQL> alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF' offline;
Database altered.
STEP 3:
------
SQL> select file_name from dba_temp_files where tablespace_name='TEMP';
FILE_NAME
-------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF
+++ Use os command to move or copy the files.
STEP 4:
------
SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.dbf'
to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.dbf';
Database altered.
Now we can check if the file is move and renamed as below,
SQL> select file_name from dba_temp_files where tablespace_name='TEMP';
FILE_NAME
------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF
STEP 5:
------
SQL> alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF' online;
Database altered.