Translate this Page

 

Sometime we need to move Temporary Files to other location. This steps will guide you how to do that easily and safety ;)

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

  One Response to “Move or Rename the Tempfile in Oracle”

  1. Hi,

    Very interesting. Nice article and discussion.
    Thanks a lot for same.

    Regards,
    Gitesh Trivedi
    http://www.dbametrix.com

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha