Interpreting Wait Event “file#” and “block#” parameters
=======================================================
This reference note describes how to interpret file# and block# parameters from wait events (eg: obtained from
&AFN to represent the absolute file number (file#)
&BLOCKNO to represent the block number (block#)
IMPORTANT: The details here assume that file# is an absolute file number.
file#
=====
The name of the file can be displayed with the following SQL:
SELECT tablespace_name, file_name
FROM dba_data_files
WHERE file_id = &AFN
;
If the file number does not appear in with this select in Oracle8i / 9i AND file# is greater than the DB_FILES parameter value then the file is probably a TEMPFILE. In this case the filename can be found using:
SELECT tablespace_name, file_name
FROM dba_temp_files f, v$parameter p
WHERE p.name=’db_files’
AND f.file_id+p.value = &AFN
;
block#
======
If the file is NOT a TEMPFILE then the following query should show the name and type of the segment:
SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &AFN
AND &BLOCKNO BETWEEN block_id AND block_id + blocks -1
;
In Oracle8 onwards add PARTITION_NAME to the select list above to obtain details of the partition (if any).
If the block# is 1 then this is typically a datafile header and the query above will return no rows.
For INDEX segments it is often desirable to know which table the index is on. Eg:
SELECT table_owner, table_name
FROM dba_indexes
WHERE owner=’&OWNER’
AND index_name=’&SEGMENT_NAME’
;
Absolute File Number
====================
In Oracle7 all file# values can be treated as the absolute file number for the queries in this note.
In Oracle8 onwards each datafile has a relative file number and an absolute file number. The relative file number is relative to the tablespace which owns the datafile. The following query will show the absolute and relative file numbers for datafiles in the database:
SELECT tablespace_name, file_id “AFN”, relative_fno “RFN”
FROM dba_data_files;
In Oracle8i onwards a database can also contain TEMPFILES. The following query will show the absolute and relative file numbers for tempfiles in the database:
SELECT tablespace_name, file_id + value “AFN”, relative_fno “RFN”
FROM dba_temp_files, v$parameter
WHERE name=’db_files’;
It is important to use the correct (absolute) file number in the queries in this note.