If a temporary file (tempfile) belonging to the TEMP tablespace is lost
or damaged, the TEMP tablespace will not be available. This problem manifests
itself as errors during the execution of SQL statements that require TEMP space
for sorting.
ERROR
at line 1:
ORA-01565:
error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27037:
unable to obtain file status
Linux
Error: 2: No such file or directory
The Oracle database can start up with a missing temporary file. If any of
the temporary files do not exist when the database is started, they are created
automatically, and the database opens normally. When this happens, a message
like the following appears in the alert log during startup:
Recreating
tempfile /u01/app/oracle/oradata/orcl/temp01.dbf
Recovering
from a TEMPFILE Loss
You can recover from a lost TEMPFILE without restarting the database.
For example, to recover the database when the temp01.dbf data file
belonging to the default temporary tablespace TEMP has been deleted at the
operating system (OS) level, add a new data file, and then drop the one that
was deleted:
SQL>
ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf'
SIZE 20M;
Tablespace altered.
SQL>
ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf';
Tablespace altered.
