ORA-01157 and ORA-01110 when trying to bring my tablespaces online

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'


How do I fix this ORA-01157 error?

ORA-01157: cannot identify/lock data file string - see DBWR trace file

Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use.

first check the datafile is available where it is trying to write the block using 

SQL> select file_name, file_id from dba_data_files where file_name like 'FILE_NAME' ;

if it returns a row with details means datafile is busy in writing some blocks at the time when Query executed , if it returns no rows means there is no DATAFILE with the given name. 

The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.


The ORA-01157 is caused by a locking issue with the database writer (DBWR) background process.  During a recovery, this can be caused by a unopened data files (i.e. database mounted but not open), a missing file, a permission problem in the file (e.g. no write permissions 770 on the files owned by Oracle).