In Oracle, if you have a file ID (file number) and block number, and you want to find the corresponding segment name (such as a table or index name), you can use the DBA_EXTENTS data dictionary view. This view maps extents (which consist of continuous blocks) to segments.
The below query can be used for that
SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = < file> AND
<block> BETWEEN block_id and block_id + blocks - 1;
Explanation:
file_id: This is the ID of the file where the block resides.
block_number: This is the block number you are querying about.
segment_name: This is the name of the segment (like a table or index) to which the block belongs.
segment_type: This indicates the type of the segment (e.g., TABLE, INDEX).
owner: This is the schema owner of the segment.
Example
SELECT segment_name, segment_type FROM dba_extents WHERE file_id = 101 AND 8758758 BETWEEN block_id and block_id + blocks - 1;
You might want to use this query in conjunction with wait events to find the segment name
SELECT event, p1text,p1 “FILE”, p2text,p2 “BLOCK”, p3text,p3 “ID” FROM gv$session_wait WHERE event ='gc current request'; or SELECT event, p1text,p1 “FILE”, p2text,p2 “BLOCK”, p3text,p3 “ID” FROM gv$session_wait WHERE event ='gc buffer busy acquire'; Or SELECT event, p1 “FILE”, p2 “BLOCK”, p3 “ID” FROM v$session_wait WHERE event = 'buffer busy waits'; Once block number and file id is known, we can find the segment using the below query SELECT segment_name, segment_type FROM dba_extents WHERE file_id = <file id> AND <block number> BETWEEN block_id and block_id + blocks - 1;
Important points
- Privileges: To run this query, you need access to the DBA_EXTENTS view. Usually, this requires DBA privileges.
- Performance Consideration: Be cautious when running this query on large databases with many extents, as it might take time and consume resources.
- This query is particularly useful for database administrators when diagnosing space-related issues or performing deep internal investigations into database storage.
I hope you like this short post on finding segment name given the file id and block number in Oracle database
Related Articles
How to find the High Watermark of the Oracle Table: The high water mark in the Oracle database is used to show the blocks which has been ever used in the life cycle of the Oracle table
How to find metadata of any objects in oracle: Check out how to get table definition in oracle, oracle show index definition, get ddl of materialized view in oracle, get the query of a view in oracle
How to find a table whose statistics are locked
oracle table size: Find out the Query to check table size in Oracle database, top ten big tables in particular schema or particular tablespace in Oracle
How to check block corruption in Oracle: visit this page on How to check block corruption in Oracle, How to Correct the Block Corruption using RMAN Blockrecover
