Move_or_Rename_the_Tempfile_in_Oracle

1. Login as SYSDBA

2. Check the Tempfile status
SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                 STATUS
---------- ------------------------- -------
 1         /u01/temp01.dbf           ONLINE
 2         /u02/temp02.dbf           ONLINE
 3         /u02/temp03.dbf           ONLINE

3. Make OFFLINE the tempfile that need to move

SQL> ALTER DATABASE TEMPFILE '/u01/temp01.dbf' OFFLINE;
Database altered.

ATTENTION :
We should make offline the tempfile that need to move, if not we will get this error :

SQL> ALTER DATABASE RENAME FILE '/u01/temp01.dbf' TO '/u02/temp01.dbf';
ALTER DATABASE RENAME FILE '/u01/temp01.dbf' TO '/u02/temp01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 1026 - file is in use or recovery
ORA-01110: data file 1026: '/u01/temp02.dbf'


4. Check the Tempfile status


SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                 STATUS
---------- ------------------------- -------
 1         /u01/temp01.dbf           OFFLINE
 2         /u02/temp02.dbf           ONLINE
 3         /u02/temp03.dbf           ONLINE
 
5. Copy the tempfile that need to move

SQL> !cp -p /u01/temp01.dbf /u02/temp01.dbf
ATTENTION :
Dont forget to copy it first before rename it, if not you will get this error :


SQL> ALTER DATABASE RENAME FILE '/u01/temp01.dbf' TO '/u02/temp01.dbf';
ALTER DATABASE RENAME FILE '/u01/temp01.dbf' TO '/u02/temp01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 1025 - new file '/u02/temp01.dbf' not found
ORA-01110: data file 1025: '/u01/temp01.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

6. Rename Tempfile that already move to other location

SQL> ALTER DATABASE RENAME FILE '/u01/temp01.dbf' TO '/u02/temp01.dbf';
Database altered.

7. Check the Tempfile status after moved to other location

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                 STATUS
---------- ------------------------- -------
 1         /u02/temp01.dbf           OFFLINE
 2         /u02/temp02.dbf           ONLINE
 3         /u02/temp03.dbf           ONLINE

8. Make ONLINE the tempfile that need to move

SQL> ALTER DATABASE TEMPFILE '/u02/temp01.dbf' ONLINE;
Database altered.

9. Check the Tempfile status after moved

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                 STATUS
---------- ------------------------- -------
 1         /u02/temp01.dbf           ONLINE
 2         /u02/temp02.dbf           ONLINE
 3         /u02/temp03.dbf           ONLINE
 
10. Remove the old Tempfile

SQL> !rm -rf /u01/temp01.dbf