This was first published on https://blog.dbi-services.com/an-alternative-to-dba_extents-optimized-for-lmt (2015-06-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view using the underlying X$ tables and constrained by hints is faster when queried for one FILE_ID/BLOCK_ID. I did that in 2006 when having lot of corruptions on several 10TB databases with 5000 datafiles.
Since then, I’ve used it only a few times, so there is no guarantee that the plan is still optimal in current version, but the approach of starting to filter the segments that are in the same tablespace as the file_id makes it optimal for a search by file_id and block_id.
Here is the creation of the DATAFILE_MAP view:
create or replace view datafile_map as 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 ) 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 ;
COLUMN partition_name ON FORMAT A16 COLUMN segment_name ON FORMAT A20 COLUMN owner ON FORMAT A16 COLUMN segment_type ON FORMAT A16 select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map where file_id=1326 and 3782 between block_id and block_id + blocks - 1 SQL> / FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME -------- -------- ------- ---------------- ---------------- ---------------- ---------------- 1326 3781 32 free space
you identified free space block
select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map where file_id=1326 and 3982 between block_id and block_id + blocks - 1 SQL> / FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME -------- -------- ------- ---------------- ---------------- -------------------- ---------------- 1326 3981 8 TABLE PARTITION TESTUSER AGGR_FACT_DATA AFL_P_211
you identified a data block
select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map where file_id=202 and 100 between block_id and block_id + blocks - 1 SQL> / FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME ---------- ---------- ---------- ---------------- ---------------- -------------------- --------------- 202 1 1280 tempfile C:O102TEMP02.DBF
you identified a tempfile file_id
select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map where file_id=1 and block_id between 0 and 100 order by file_id,block_id; FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME ---------- ---------- ---------- ---------------- ---------------- -------------------- --------------- 1 9 8 ROLLBACK SYS SYSTEM 1 17 8 ROLLBACK SYS SYSTEM 1 25 8 CLUSTER SYS C_OBJ# 1 33 8 CLUSTER SYS C_OBJ# 1 41 8 CLUSTER SYS C_OBJ# 1 49 8 INDEX SYS I_OBJ# 1 57 8 CLUSTER SYS C_TS# 1 65 8 INDEX SYS I_TS# 1 73 8 CLUSTER SYS C_FILE#_BLOCK# 1 81 8 INDEX SYS I_FILE#_BLOCK# 1 89 8 CLUSTER SYS C_USER# 1 97 8 INDEX SYS I_USER#
you mapped the first segments in system tablespace
Try it on a database with lot of segments and lot of datafiles, and compare with DBA_EXTENTS. Then you will know which one to choose in case of emergency.