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
Share To The World 