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;
|
