Many times, you might need to determine the segment name given the file id and block number. 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;
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 waits 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;
I hope you like this short post on find segment name from block in oracle
Related Articles
- How to find the High Water mark of the Oracle Table
- Downtime Reduction during Patching
- How to find metadata of any objects in oracle
- How to find table whose statistics are locked
Leave a Reply