Sunday, April 1, 2012

Extract SQL from trace file with Perl

Perl is included with the Oracle database software (SE and EE), even on Windows. I wrote this to extract the SQL statements from a trace file (generated with sql_trace=true or setting the 10046 event). A simple indentation - one tab for each level - is used to show recursive statements.

while (<>) {
($level)=$_=~/\s+dep=(\d+)\s+/ ;
while ($line!~/END OF STMT/){
for($i=0;$i<$level;$i++) {
print "\t" ;
print $line;
print "\n" ;

You usually have to expand your PATH on Windows to find Perl; you'll find Perl.exe somewhere below %ORACLE_HOME%. The code will of course work on Linux and other OS where you have Perl. Store the code above in a file called and call it with:

perl your_trace_file.trc | more

There is an option (record=filename.sql) in tkprof to extract the SQL, but it does not include recursive statements. I guess the difference is that this is something to build on when you have a more complex task.

No comments :