Loss of TEMP Datafile:
Symptoms: On large sorts (select distinct, order by, group by, union) that can't be done in memory, the sort will fail with "ORA-01157: cannot identify data file 3 - file not found" if the loss happened in the middle of the sort, or "ORA-01116: error in opening database file 3" if the loss happened before the sort started, along with the file name "ORA-01110: data file 3: '/u03/oradata/PROD/temp_PROD_01.dbf'". Nothing is put into the alert.log file, and no trace files will be generated for this.

sqlplus "/ as sysdba"
SQL> alter database datafile 'E:\ORACLE\ORADATA\DBORA\TEMP01.DBF’ offline;
Then, if from mount state, do an "alter database open;" here.
SQL> select file_name, bytes/1024 kbytes from dba_temp_files;
Shows the size of the TEMP datafile(s) in Kbytes.
SQL> select initial_extent/1024 kbytes from dba_tablespaces
where tablespace_name = 'TEMP';
Shows the uniform size of the extents in Kbytes.
SQL> drop tablespace temp;
SQL> delete E:\ORACLE\ORADATA\DBORA\TEMP01.DBF
SQL> create tablespace temp
tempfile ' E:\ORACLE\ORADATA\DBORA\TEMP01.DBF' size 2655232
extent management local uniform size 1024K;

you must change the path according to your physical drive paths and change the DBORA, which is the database files folder.