Wednesday, February 10, 2016

ORA-01114: IO error writing block to file

ORA-01114 error indicates that the physical space where the datafile or tempfile exist has been exhausted. To resolve this issue you have to identify the mount point for which this error is coming.

whenever there is any ORA-01114 error in your database, then you first have to check the file number associated with this error. The file number may be datafile number or tempfile number.
If it is a datafile then following query will give you the mount point where the datafile exist.

SQL> select file_id, file_name from dba_data_files where file_id=;

If there is no output coming in the above query then the file number will be tempfile. 
Whenever the file mentioned in errors like ‘ORA-01114’ is a tempfile the file id is `file_id` + `the value of the DB_FILES of init parameter`.
Or;
IF file_id > DB_FILE THEN
    file_type := 'tempfile';
    file_id := DB_FILES - file_id ;

Followers