八
01
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;
no comment untill now