Whilst creating a new 10GB index on a large 40GB table I was experiencing the
“ORA-01114: IO error writing block to file <file#> (block # <block#>)” error.
The main vital piece of information is the file number (<file#>). This can be found in the DBA_DATA_FILES or DBA_TEMP_FILES views as the FILE_ID column.
So, you construct a query to go find the file name:
SELECT file_id, file_name
FROM dba_data_files
WHERE file_id = <the number>
UNION ALL
SELECT file_id, file_name
FROM dba_temp_files
WHERE file_id = <the number>;
NOTE: I query both just in case. I’ve worked on systems where the TEMP tablespace has not been created as “TEMPORARY”!
After running the query, you get “0 rows returned”.
What!
Well, in this case, the TEMP tablespace was a proper TEMPORARY tablespace as the file_id was returned from the DBA_TEMP_FILES view.
I knew that the file numbers were somehow linked to the number of permitted files in the database. This is controlled by the DB_FILES database parameter.
Looking at the Oracle documentation (for 10g) here: https://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm#i1206149
You will see that Oracle states that:
“…the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.”
In short this means that the DB_FILES parameter only applies to datafiles and not tempfiles.
Therefore, all other files will be outside this parameter range.
My parameter DB_FILES was set to 1024.
My reported file# in the ORA-01114 error was greater than 1024.
If you look at the file_id values for the DBA_TEMP_FILES view, you’ll notice that the file_id numbers conflict with the file_id values in DBA_DATA_FILES.
Since the tempfiles exist outside the range of DB_FILES, I subtracted the value of parameter “DB_FILES” from the reported file#, to achieve the file_id for tempfiles belonging to temporary tablespaces.
Voila!
Sure enough, when I subtracted 1024 (my DB_FILES value) from my reported file id in the ORA-01114, I got 2, which equated to the second tempfile in the TEMP tablespace.
The partition that the tempfile was sitting in was 95% full, but the tempfile couldn’t expand by the requested next extent size as it would be more than the partition free space. Increasing the partition size fixed my problem.
Is there another way of viewing the online file# vs the DBA_x_FILES file_id field? I don’t know, but if I find one, I’ll post it here.
