[20160815]查询相关表的sql语句.txt
--以前我要查询相关表的sql语句，我一般在toad下执行sga trace，直接在sql search text 输入表名，但是这里存在一个问题，仅仅检
--索sql语句的前1000个字节才有效。
--如果我要查询的表在1000字符外，无法查询，通过例子说明：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ cat b.sql
select /*+ aaaa */ * from dept;
--在*号前插入1000个空格。
SCOTT@book> @ b.sql
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--//如果在toad查询，直接查询dept无法找到对应语句，查询aaaa就可以查询到。
--通过如下脚本，链接如下：http://www.anbob.com/archives/2184.html,补充我做了小量修改。
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 a158 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
SYS@book> @ &r/ref_t
Please enter Name of Table Owner : scott
Please enter Table Name to show reference SQLs for: dept
SQL_ID DISK_READS LOGICAL_READS EXECUTIONS SQL_TEXT
------------- ---------- ------------- ---------- ----------------------------------------------------------
fzdxjssfcwu4r 0 85 4 select /*+ aaaa */ *
--缺点必须以sys用户执行。