ORA-01157: cannot identify/lock data file 201

oracleoracle-10goracle-11g

SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
                                   *

ERROR at line 1: ORA-01157: cannot identify/lock data file 201 – see
DBWR trace file ORA-01110: data file 201: '/oradata_nocg/temp01.dbf'

In the database I don't have '/oradata_nocg/temp01.dbf', I am not doing any operation related temp tablespace Why it is not allowing run above select query. ?

Best Answer

Most likely this is in a database that was cloned from an other database and the tempfiles are not added. Oracle needs a functional temporary tablespace to be able to process joins and sorts. dba_temp_files is a view that does this.

Solution is to simply add a few tempfiles to the temporary tablespace.