dbms_sqldiag.dump_trace

网上看到lfree大佬写的脚本，这里整理下。整合到shell脚本中使用更加便捷,原文地址如下：

http://blog.itpub.net/267265/viewspace-2893172/

一：原SQL文本

#这里有一个不好的地方就是不自动打印trc文件，需要到目录中去找

execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

 

二：整理shell脚本如下

if [ "$DBUSER" = "" ]; then
DBUSER="/ as SYSDBA"
fi

if [ "$1" = "sqldiag" -o "$1" = "10053" ]; then

if [ "$2" = "" ]; then
echo "ora 10053/sqldiag sql_id child_number"
exit 0
fi
sqlplus -s /NOLOG << !
connect $DBUSER
set serveroutput on
declare
l_10053path clob := null;
begin
dbms_sqldiag.dump_trace(p_sql_id=>'$2',p_child_number=>$3,p_component=>'Compiler',p_file_id=>'a'||'$2');
select pr.value || '/' || i.instance_name || '_ora_' || to_char(ps.spid) ||'_a'|| '$2' ||
'.trc' "trace file name"
into l_10053path
from v\$session s, v\$process ps, v\$diag_info pr, v\$instance i
where s.paddr = ps.addr
and s.sid = userenv('sid')
and pr.name = 'Diag Trace';
dbms_output.put_line(l_10053path);
end;
/
!
exit 0
fi

三：验证测试

[oracle@oracle11g ~]$ ora 10053
ora 10053/sqldiag sql_id child_number
[oracle@oracle11g ~]$ ora 10053 5yv7yvjgjxugg 0
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3168_a5yv7yvjgjxugg.trc

PL/SQL procedure successfully completed.

[oracle@oracle11g ~]$ ll /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3168_a5yv7yvjgjxugg.trc
-rw-r----- 1 oracle oinstall 123839 May 11 08:50 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3168_a5yv7yvjgjxugg.trc