By Franck Pachot

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

The script

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
;

Sample output

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.

Post Views: 408
Thumbnail [60x60]
by
Oracle Team