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 (<>) {
if(/^PARSING IN CURSOR/) {
($level)=$_=~/\s+dep=(\d+)\s+/ ;
$line=<>;
while ($line!~/END OF STMT/){
for($i=0;$i<$level;$i++) {
print "\t" ;
}
print $line;
$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 xtract.pl and call it with:


perl xtract.pl 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.