Working with Oracle, using TEMPORARY tablespaces with tempfiles is best practice today. Unfortunately there are two situations in which you may encounter trouble as for the tempfiles are missing and sort operations do no longer succeed.

Curiously and based on the fact that Oracle does not record checkpoint information in tempfiles, Oracle can even start up a database with a missing tempfile.

DBW0 will write to a trace file indicating the tempfile is not found, but the database opens normally and later on only sort operations will fail with some of the Oracle Errors ORA-01116, ORA-01157 and ORA-25153.

Such situation can be caused by simply the OS file related to a tempfile cannot be not found anymore or the logical tempfiles attached to the TEMPORARY tablespace do not exist anymore.

This can happen in case the tempfile was located on a disk that crashed, had a bad controller, or some other type of media failure, a recovery or cloning of the database was done before and/or finally and purely caused by human error.

Solution : Drop the logical tempfile and add a new one

SQL> alter database tempfile ‘/oracle/oradata/SID/tempfile.tmp’ drop;

Database altered.

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected.

SQL> alter tablespace temp add tempfile ‘/oracle/oradata/SID/tempfile.tmp’ size 100m;

Tablespace altered.