[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
--這樣可以對齊顯示。順便貼上原始碼！



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