[20131031]从跟踪文件中抽取sql语句.txt

[20131031]从跟踪文件中抽取sql语句.txt

以前写过利用tkprof顺序抽取执行的sql语句
http://space.itpub.net/267265/viewspace-748041
但是递归语句没有记录下来。

今天看了一篇blog，记录如下：
--说明：我修改2处：dept那行里面的－1原文有错，应该是半角-1。
--printf "%s %s %s>>>> %s\n", stmt_count, depth_level, cursor_no, $0
--我修改为
--printf "%04s %s %s>>>> %s\n", stmt_count, depth_level, cursor_no, $0
--这样可以对齐显示。顺便贴上源代码！

http://oraclue.com/2008/10/09/script-to-extract-sql-code-from-trace-file-trimsqlsh/

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';

trimsql.sh
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
    }
}'