How To Move Or Rename The Tempfile In Oracle
Log in SYSDBA Check 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 /appsolworld/temp01.dbf ONLINE 2 /appsolworld/temp02.dbf ONLINE 3 /appsolworld/temp03.dbf ONLINE Make it OFFLINE the tempfile that has to move SQL> ALTER DATABASE TEMPFILE '/appsolworld/temp01.dbf' OFFLINE; We should make offline the tempfile that has to move, if not then will get this error : SQL> ALTER DATABASE RENAME FILE '/appsolworld/temp01.dbf' TO '/u02/temp01.dbf'; ALTER DATABASE RENAME FILE '/appsolwolrd/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: '/appsolworld/