Solution for ORA-01157 ORA-01110 error

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/STLBAS/temp01.dbf'


1. Connect to sqlplus

SQL> select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files;
select tablespace_name, file_name, bytes/1048576 "MB" 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: '/u01/app/oracle/oradata/STLBAS/temp01.dbf'

2. Check the status of datafile

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         1 ONLINE
/u01/app/oracle/oradata/STLBAS/temp01.dbf


3. Drop temporary datafile

SQL> alter database tempfile '/u01/app/oracle/oradata/STLBAS/temp01.dbf' drop;

Database altered.

SQL> select file#,status,name from v$tempfile;

no rows selected

SQL>
4. Create new temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE   '/oracledb/oradata/stlbas/temp02.dbf' SIZE 10G;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.