This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Negative Values in V$TEMP_SPACE_HEADER

Whilst trying to figure out a reliable method of monitoring temporary tablespace usage I came across this problem.

The v$temp_space_header bytes_used column was displaying negative values.

Metalink has document 467435.1 which states quite simply that in and later, the temp file header(s) may have been corrupted.

The solution, drop and re-create the temp file.

ORA-01114 IO Error But What Is The Oracle File

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>
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”.

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:

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.

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.