麻袋爸爸

1. 脚本一

select distinct a.owner, a.segment_name
  from dba_extents a,
       (select dbarfil, dbablk
          from x$bh
         where hladdr in
               (select addr
                  from (select addr from v$latch_children order by sleeps desc)
                 where rownum < 11)) b
 where a.RELATIVE_FNO = b.dbarfil
   and a.BLOCK_ID <= b.dbablk
   and a.block_id + a.blocks > b.dbablk;

2.脚本二

select object_name
  from dba_objects
 where data_object_id in
       (select obj
          from x$bh
         where hladdr in
               (select addr
                  from (select addr from v$latch_children order by sleeps desc)
                 where rownum < 11));


select distinct a.owner, a.segment_name, a.segment_type
  from dba_extents a,
       (select dbarfil, dbablk
          from (select dbarfil, dbablk from x$bh order by tch desc)
         where rownum < 11) b
 where a.RELATIVE_FNO = b.dbarfil
   and a.BLOCK_ID <= b.dbablk
   and a.block_id + a.blocks > b.dbablk;

3. 脚本三

select object_name
  from dba_objects
 where data_object_id in (select obj
                            from (select obj from x$bh order by tch desc)
                           where rownum < 11);

4. 脚本四

select sql_text
  from v$sqltext a,
       (select distinct a.owner, a.segment_name, a.segment_type
          from dba_extents a,
               (select dbarfil, dbablk
                  from (select dbarfil, dbablk from x$bh order by tch desc)
                 where rownum < 11) b
         where a.RELATIVE_FNO = b.dbarfil
           and a.BLOCK_ID <= b.dbablk
           and a.block_id + a.blocks > b.dbablk) b
 where a.sql_text like '%' || b.segment_name || '%'
   and b.segment_type = 'TABLE'
 order by a.hash_value, a.address, a.piece;

5. 脚本五

select sql_text
  from stats$sqltext a,
       (select distinct a.owner, a.segment_name, a.segment_type
          from dba_extents a,
               (select dbarfil, dbablk
                  from (select dbarfil, dbablk from x$bh order by tch desc)
                 where rownum < 11) b
         where a.RELATIVE_FNO = b.dbarfil
           and a.BLOCK_ID <= b.dbablk
           and a.block_id + a.blocks > b.dbablk) b
 where a.sql_text like '%' || b.segment_name || '%'
   and b.segment_type = 'TABLE'
 order by a.hash_value, a.address, a.piece;

6. 脚本六

select /*+ rule*/
 owner, object_name
  from dba_objects
 where data_object_id in (select obj
                            from (select obj from x$bh order by tch desc)
                           where rownum < 11);

7. 脚本七

select sid,
       event,
       p1 as file_id,
       p2 as "block_id/latch",
       p3 as blocks,
       l.name
  from v$session_wait sw, v$latch l
 where event not like '%SQL%'
   and event not like '%rdbms%'
   and event not like '%mon%'
   and sw.p2 = l.latch#(+);

8. 脚本八

select sw.sid, event, l.name, de.segment_name
  from v$session_wait sw, v$latch l, dba_extents de
 where event not like '%SQL%'
   and event not like '%rdbms%'
   and event not like '%mon%'
   and sw.p2 = l.latch#(+)
   and sw.p1 = de.file_id(+)
   and p2 between de.block_id and de.block_id + de.blocks - 1;