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.