DBI的Franck Pachot给出了一个更高效率的DBA_EXTENTS脚本，对于特别大的ORACLE数据库定位EXTENT时很有用：
column owner format a6
column segment_type format a20
column segment_name format a15
column partition_name format a15
set linesize 200
set timing on time on echo on autotrace on stat
WITH
l AS ( /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */
SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn,
ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno
FROM sys.x$ktfbue
),
d AS ( /* DMT extents ts#, segfile#, segblock# */
SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn,
block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno
FROM sys.uet$
),
s AS ( /* segment information for the tablespace that contains afn file */
SELECT /*+ materialized */
f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize
FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2
WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn
),
m AS ( /* extent mapping for the tablespace that contains afn file */
SELECT /*+ use_nl(e) ordered */
s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
FROM s,l e
WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
UNION ALL
SELECT /*+ use_nl(e) ordered */
s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
FROM s,d e
WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
UNION ALL
SELECT /*+ use_nl(e) use_nl(t) ordered */
f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t
WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn
UNION ALL
SELECT /*+ use_nl(e) use_nl(t) ordered */
f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t
WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn
),
o AS (
SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block segbid,s.segment_type,s.owner,s.segment_name,s.partition_name
FROM SYS_DBA_SEGS s
),
datafile_map as (
SELECT
afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,
owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,
tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid
FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)
UNION ALL
SELECT
file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,
1 block_id,blocks,'tempfile' segment_type,
'' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,
tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid
FROM dba_temp_files
)
select * from datafile_map where file_id=5495 and 11970455 between block_id and block_id+blocks
