Object list via File and Block ID.

Oracle Object list via File and Block ID. ..

SELECT p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait

WHERE event ='buffer busy waits';

set pause on pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25
select segment_name, segment_type, block_id, blocks from dba_extents where file_id = &file_no AND ( &block_value between block_id and ( block_id + blocks ) )

SELECT p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait
WHERE event ='buffer busy waits';	

set pause on pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25
select con_id,segment_name, segment_type, block_id, blocks 
from cdb_extents 
where file_id = &file_no
 AND ( &block_value between block_id and ( block_id + blocks ) )
 /

================================

set pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25

select 
  TO_CHAR(con_id) as con_id, -- Convert CON_ID (NUMBER) to CHAR
  segment_name, 
  segment_type, 
  block_id, 
  blocks 
from cdb_extents 
where file_id = &&file_no 
 AND ( &&block_value between block_id and ( block_id + blocks ) )
union all
select 
  ' ' as con_id, -- Now matching the CHAR datatype
  segment_name, 
  segment_type, 
  block_id, 
  blocks 
from dba_extents 
where file_id = &&file_no 
  AND ( &&block_value between block_id and ( block_id + blocks ) )
/ 

 undefine file_no block_value


or 

set pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25

select 
  con_id, 
  segment_name, 
  segment_type, 
  block_id, 
  blocks 
from cdb_extents 
where file_id = &&file_no 
  AND ( &&block_value between block_id and ( block_id + blocks ) )
union all
select 
  CAST(NULL AS NUMBER) as con_id, -- CAST NULL to NUMBER to match con_id
  segment_name, 
  segment_type, 
  block_id, 
  blocks 
from dba_extents 
where file_id = &&file_no 
  AND ( &&block_value between block_id and ( block_id + blocks ) )
/