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