[20230518]建立完善find_objz.sql脚本.txt

来源：这里教程网时间：2026-03-03 18:50:02 作者：

[20230518]建立完善find_objz.sql脚本.txt --//Franck Pachot有一个脚本用来查询属于那个数据段,比查询dba_extents要快. --//以前我把它命名为find_obj.sql .查询需要输入参数1=file_id 参数2=block_id. --//我想利用这个脚本建立更加通用的版本,比如可以输入任意条件的查询语句. --//可以使用它查询一定范围的块有那些对象,查询需要输入参数1=file_id 参数2=开始块参数3=结束块. --//利用参数4控制条件的选择,例如: --//参数4=1,选择file_id = &&1 AND &&2 BETWEEN block_id AND block_id + blocks - 1 --//参数4=2,选择file_id = &&1 and block_id between &&2 and &&3 order by file_id,block_id --//参数4=3,选择&&1, 自己写查询条件,比如"owner='SCOTT' and segment_name='EMP'" 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 define noprint='noprint' col tpt_comment1 &noprint new_value _tpt_comment1 col tpt_comment2 &noprint new_value _tpt_comment2 col tpt_comment3 &noprint new_value _tpt_comment3 col tpt_noprint &noprint new_value _tpt_noprint set term off select decode(&&4,1,'','--') tpt_comment1 ,decode(&&4,2,'','--') tpt_comment2 ,decode(&&4,3,'','--') tpt_comment3 from dual; set term on 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 1=1 and &&_tpt_comment1 file_id = &&1 AND &&2 BETWEEN block_id AND block_id + blocks - 1 &&_tpt_comment2 file_id = &&1 and block_id between &&2 and &&3 order by file_id,block_id &&_tpt_comment3 &&1 ;