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 ) )
/

************************************************

set linesize 300 pagesize 300
define bdate='25:01:2026 04:46'
define edate='25:01:2026 05:10'
SELECT event, p1 "File#", p2 "Block#", p3 "Class#", COUNT(*) AS waits
FROM V$ACTIVE_SESSION_HISTORY
WHERE 1=1
and event = 'control file sequential read'
and SAMPLE_TIME BETWEEN TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI') and TO_DATE('&edate', 'DD-MM-YYYY HH24:MI')
GROUP BY event, p1, p2, p3
ORDER BY waits DESC;



SELECT event, p1 "File#", p2 "Block#", p3 "Class#",
       COUNT(*) AS waits
FROM v$session_wait
WHERE event = 'control file sequential read'
GROUP BY event, p1, p2, p3
ORDER BY waits DESC;

set linesize 300 pagesize 300

DEF   3="TIMESTAMP'2026-02-02 05:00:00'"
DEF   4="TIMESTAMP'2026-02-02 06:00:00'"
define bdate='25:01:2026 04:46'
define edate='25:01:2026 05:10'

col Block#  for 99999999999999999
col "File#" for 9999999999999999
SELECT event, p1 "File#", p2 "Block#", p3 "Class#", COUNT(*) AS waits
FROM V$ACTIVE_SESSION_HISTORY
WHERE 1=1
--and event = 'control file sequential read'
--and SAMPLE_TIME BETWEEN TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI') and TO_DATE('&edate', 'DD-MM-YYYY HH24:MI')
AND sample_time BETWEEN &3 AND &4
GROUP BY event, p1, p2, p3
ORDER BY waits DESC;




set linesize 300 pagesize 300

DEF   3="TIMESTAMP'2026-02-02 05:00:00'"
DEF   4="TIMESTAMP'2026-02-02 06:00:00'"
define bdate='02:01:2026 04:46'
define edate='02:01:2026 05:10'

col Block#  for 99999999999999999
col "File#" for 9999999999999999
SELECT sql_id,event, p1 "File#", p2 "Block#", p3 "Class#", COUNT(*) AS waits
FROM V$ACTIVE_SESSION_HISTORY
WHERE 1=1
--and event = 'control file sequential read'
--and SAMPLE_TIME BETWEEN TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI') and TO_DATE('&edate', 'DD-MM-YYYY HH24:MI')
AND sample_time BETWEEN &3 AND &4
GROUP BY sql_id,event, p1, p2, p3
having COUNT(*) >2
ORDER BY waits DESC;

👉 Use this to find the file# and block# causing the wait, then:

SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = &File#
AND &Block# BETWEEN block_id AND block_id + blocks - 1;

Find top SQL causing full scans:

1
2
3
4
SELECT sql_id, executions, buffer_gets, disk_reads,sql_text
FROM gv$sql
WHERE sql_id IN (SELECT sql_id FROM v$session WHERE event = 'db file scattered read')
ORDER BY disk_reads DESC;

set linesize 300 pagesize 300

DEF   3="TIMESTAMP'2026-02-09 05:00:00'"
DEF   4="TIMESTAMP'2026-02-10 06:00:00'"
define bdate='02:01:2026 04:46'
define edate='02:01:2026 05:10'

col Block#  for 99999999999999999
col "File#" for 9999999999999999
SELECT sql_id,event, p1 "File#", p2 "Block#", p3 "Class#", COUNT(*) AS waits
FROM dba_hist_active_sess_history
WHERE 1=1
--and event = 'control file sequential read'
--and SAMPLE_TIME BETWEEN TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI') and TO_DATE('&edate', 'DD-MM-YYYY HH24:MI')
AND sample_time BETWEEN &3 AND &4
GROUP BY sql_id,event, p1, p2, p3
having COUNT(*) >2
ORDER BY waits DESC



undef file_id
undef block_id
undef tsname

define file_id=11
col ts_name new_value tsname

select 	 ts.name ts_name
from 	 v$tablespace ts
	,v$datafile df
where	 file#	= &&file_id
and	 ts.ts#	= df.ts#
;

TS_NAME
------------------------------
SYSAUX

===

define file_id=11
define block_id=66319
define tsname='SYSAUX'

set termout on
set heading off
col a	format a77 	fold_after

select 	 'File number   : '||&&file_id	a
	,'Block number  : '||&&block_id a
	,'Owner         : '||owner a
	,'segment name  : '||segment_name a
	,'Segment type  : '||segment_type a
	,'Partition name: '||partition_name a
	,'Tablespace    : '||e.tablespace_name a
	,'File name     : '||f.file_name a
from	 dba_extents e
	,dba_data_files f
where	 e.file_id	= f.file_id
and	 e.file_id	= &&file_id
and	 e.block_id	<= &&block_id
and	 e.block_id + e.blocks	> &&block_id
and	 e.tablespace_name = '&&tsname'
;