ANBOB™

PROMPT List all SQL in the library cache cursor referencing a given table
-- Author: weejar
--
-- you need to run this script either as SYS or
--  need to have relevant X$ proxy views created

-- I tested in oracle 11g it worked .


undefine owner
undefine table_name
prompt
accept owner prompt 'Please enter Name of Table Owner : '
accept table_name  prompt 'Please enter Table Name to show reference SQLs for: '

column sql_text format a58 word_wrapped
select /*+ ordered use_hash(d) use_hash(c) */
    c.kglobt03 sql_id,
    sum(c.kglobt13) disk_reads,
    sum(c.kglobt14) logical_reads,
    sum(c.kglhdexc) executions,
    c.kglnaobj sql_text
from
    sys.x$kglob o,
    sys.x$kgldp d,
    sys.x$kglcursor c
where
    o.inst_id = userenv('Instance') and
    d.inst_id = userenv('Instance') and
    c.inst_id = userenv('Instance') and
    o.kglnaown = upper(nvl('&Owner',user)) and
    o.kglnaobj = upper('&Table_name') and
    d.kglrfhdl = o.kglhdadr and
    c.kglhdadr = d.kglhdadr
group by
    c.kglnaobj,c.kglobt03
order by 3;
/

undefine owner
undefine table_name
clear breaks

reference “老白” article.

打赏

微信扫一扫，打赏作者吧～