I saw this issue when the tempfile was moved accidently at the operating system level.It was throwing following error in the alert log.
data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-12012: error on auto execute of job 449748
ORA-01116: error in opening database file ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-06512:
ORA-27041: unable to open file
ORA-12012: error on auto execute of job 449748
ORA-01116: error in opening database file ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-06512:
The resolution is to drop and add the tempfile but when I tried to drop I recieved the following error.
ORA-25152:
TEMPFILE cannot be dropped at this time
I used the following steps to identify the SID using the tempfile .After killing the SID I was able to drop and recreate the tempfile.
1) SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;
2) select sid, username, osuser, program, machine from v$session where sid in
();
(
No comments:
Post a Comment