网上看到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