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:
SELECTsql_id, executions, buffer_gets, disk_reads,sql_textFROMgv$sqlWHEREsql_idIN(SELECTsql_idFROMv$sessionWHEREevent ='db file scattered read')ORDERBYdisk_readsDESC;
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'
;
