Missing Tempfile in Oracle Database | ORA - 25153 Temporary Tablespace is Empty

Missing Temporary File Scenarios | ORA - 25153 Temporary Tablespace is Empty

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/oracle/app/product/10.1.0/db_1/dbs/D:ORACLEPRODUCT10.1.0ORADATATEXERPTEMP1.DBF'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

I have covered the 2 most common scenarios where you might be getting trouble with temporary tablespace because of missing tempfiles.

Scenario 1: Temporary file (tempfile) is lost at OS Level. But for database, it is still there.
To understand this simply, it means physically the file is somehow lost for your Oracle Database. OR your Oracle Database is unable to find the Temporary file in the location for which the record is maintained in your Oracle Environment. For example:

SQL> select * from dba_objects order by object_name;
     select * from dba_objects order by object_name
              *
     ERROR at line 1:
     ORA-01115: IO error reading block from file 201 (block # 3)
     ORA-01110: data file 201: '/oracle/oradata/ORCL/temp2_01.tmp'
     ORA-27041: unable to open file
     SVR4 Error: 2: No such file or directory
     Additional information: 3

As long as you are Starting Up or Shutting Down your Oracle Database, it won't stop you from doing the same. Only it will notify you in the trace file regarding missing temporary file.

Solution: Drop the temporary file at database level and add a new one

      SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp' drop;
      Database altered.

      SQL> select tablespace_name, file_name from dba_temp_files;
      no rows selected.

      SQL> alter tablespace temp2
        2  add tempfile '/oracle/oradata/ORCL/temp2_01.tmp' size 5m;
      Tablespace altered.


Scenario 2: The Tempfile is lost at Database Level. In simple words, somehow you have dropped the tempfile using the DROP command at database level and now the database is missing temporary file.
In this case, when you'll get an error like:

 SQL> alter table test add primary key (c);
     alter table test add primary key (c)
     *
     ERROR at line 1:
     ORA-25153: Temporary Tablespace is Empty

Solution : Simply add a new tempfile to overcome this type of situation.

      In 8i : Remove the OS tempfile before adding a new tempfile

      From 9i : Add a new tempfile straight forward
      To add a tempfile :
      SQL> alter tablespace TEMP_TEMPFILE_LOCAL
          2   add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';

You can get more information on the same by visiting the below mentioned Oracle Support Link:

Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?