Sunday, March 18, 2012

ORA-01114: File reported not found in dba_data_files and v$datafile

We got an ORA-01114 error:
ORA-01114: IO error writing block to file  (block # )
ORA-01114: IO error writing block to file 5006 (block # 755027)

There is no file with FILE_ID = 5006 in dba_ data_files of with file#=5006 in v$datafile:
SQL> SELECT * FROM DBA_DATA_FILES WHERE FILE_ID = 5006;
no rows selected

SQL> SELECT * FROM V$DATAFILE WHERE FILE# = 5006;
no rows selected

That's, because the datafile associated with this error belongs to a temporary tablespace (temp file) and information about temp files is stored in dba_temp_files and v$tempfile.

To find your temp file, you need to find the value of initialization parameter "db_files":
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_files';
NAME          VALUE
------------  -----
db_files      5000

In our case, the temp file is the one with file_id equal to 5006-5000 = 6.
The following queries will return temp file's info:
SELECT *
FROM DBA_TEMP_FILES
WHERE FILE_ID = 5006 - (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files');

SELECT *
FROM V$TEMPFILE
WHERE FILE# = 5006 - (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files');

No comments:

Post a Comment