Miladin Modrakovic's Blog: Oraclue

Script to extract sql code from trace file generated using event 10046 e.g.

SQL> oradebug setmypid

SQL> oradebug event 10046 trace name context forever, level 12

or

SQL> alter session set events ’10046 trace name context forever, level 12′;

Find  a trace:

SQL> oradebug tracefile_name;
/ora-main/app/oracle/admin/trace/demo11g_ora_19736.trc

Then run script like: 

trimsql.sh  <tracefile_name>   

Output will look something like :

-sh-3.1$ ./trimsql.sh demo11g_ora_19736.trc
1 1 #2>>>> select text from view$ where rowid=:1
2 1 #2>>>> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
3 0 #1>>>> select * from dba_users
4 0 #2>>>> select * from dual
5 1 #2>>>> select text from view$ where rowid=:1
6 0 #1>>>> select * from dba_tables where owner=’SYSTEM’
7 0 #2>>>> select * from dual

 Here is actual script:

——————————————————————————

cat $1 | awk ‘BEGIN {
# Initialization.
parse_line = 0
stmt_count = 0
depth = ‘${2:–1}’
}
{
    # Performed on each row of the input file.
    if (($1 == “PARSING”) && ($2 == “IN”) && ($3 == “CURSOR”)) {
         parse_line=1
         stmt_count++
         cursor_no = $4
         depth_level = substr($6, 5)
    }
    else if (($1 == “END”) && ($2 == “OF”) && ($3 == “STMT”)) {
         parse_line=0
    }

    if (($1 == “ERROR”) || (($1 == “PARSE”) && ($2 == “ERROR”))) {
         printf “%s\n”, $0
    }

    if ((parse_line == 2) && (depth >= depth_level || depth == -1)) {
         printf “%s %s %s>>>> %s\n”, stmt_count, depth_level, cursor_no, $0
    }

    if (parse_line == 1) {
         parse_line = 2
    }
}’