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. ?

  • First you check your temp file is exist on specific location or not. from this query SQL>select file#,status,name from v$tempfile; and SQL> select * from v$tempfile; Sep 10 '15 at 5:28

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.