1
更高效的DBA_EXTENTS 查询脚本
source link: https://www.askmac.cn/archives/%E6%9B%B4%E9%AB%98%E6%95%88%E7%9A%84dba_extents-%E6%9F%A5%E8%AF%A2%E8%84%9A%E6%9C%AC.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
更高效的DBA_EXTENTS 查询脚本
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK