Ora 01114 io error writing block to file block

I got ” ORA-01114: IO error writing block to file string (block # string) ” error in Oracle database.

ORA-01114: IO error writing block to file string (block # string)

Details of error are as follows.

ORA-01114: IO error writing block to file string (block # string)

Cause: The device on which the file resides is probably offline. If the file is 
a temporary file, then it is also possible that the device has run out of space. 
This could happen because disk space of temporary files is not necessarily allocated at file creation time.

Action: Restore access to the device or remove unnecessary files to free up space.

IO error writing block to file string (block # string)

This ORA-01114 errors are related with the Temp file Header of one of the Temporary Tablespace files is corrupted.

 
SELECT * FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
TEMP 4 -9,641,656,320 -1,176,960 17,157,849,088 2,094,464 3 <<<<<<<< -ve (Negative) Values in BYTES_USED & BLOCKS_USED indicates Tempfile Header Corruption
TEMP 5 7,516,192,768 917,504 0 0 4
TEMP 1 6,262,095,872 764,416 180,355,072 22,016 1
TEMP 3 6,442,450,944 786,432 0 0 2

In the V$TEMP_SPACE_HEADER, there are negative values for some of the columns indicates that the temp file header have been corrupted and so when application is trying to access these corrupt header might have got the error ” ORA-01114 error writing block to file” at Application Logs/Screen.

Action plan to fix:
Please drop/delete this temp file (example in our case: FILE_ID 4 ) and create new temp file to resolve the issue. ( Please check the output of SELECT * FROM V$TEMP_SPACE_HEADER; from all the Nodes and drop or delete and create new temp files if any -ve values are seen for any columns )

Example/syntax:

 SQL> ALTER DATABASE TEMPFILE '/<path>/temp04.dbf' DROP INCLUDING DATAFILES;

Database altered.

 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/<path>/temp04.dbf' SIZE 7G;

Tablespace altered.

 SQL> SELECT * FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE
------------------------------ ---------- ---------- ----------- ----------
BLOCKS_FREE RELATIVE_FNO
----------- ------------
TEMP 1 6215958528 758784 226492416
  27648 1

TEMP 2 1048576 128 7515144192
  917376 3

TEMP 3 6442450944 786432 0
  0 2

TEMP 5 7516192768 917504 0
  0 4

Or Restore access to the device or remove unnecessary files to free up space.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 1,157 views last month,  1 views today