使用DBMS_TRACE對PL/SQL進行跟蹤

可以使用DBMS_TRACE對PL/SQL進行跟蹤，典型的跟蹤過程如下：
*開始PL/SQL跟蹤(DBMS_TRACE.SET_PLSQL_TRACE)
*執行PL/SQL
*停止PL/SQL跟蹤(DBMS_TRACE.CLEAR_PLSQL_TRACE(trace_level))

1，簡介
1.1可以透過debug方式編譯開啟對特定程式單元的跟蹤，方式如下：

alter session set plsql_debug=true;
create or replace ... /* create the library units - debug information will be generated */

或:

/* recompile specific library unit with debug option */
alter [PROCEDURE | FUNCTION | PACKAGE BODY]  compile debug;

1.2可以透過如下引數限制資料庫儲存最近8192條跟蹤記錄：
SET_PLSQL_TRACE儲存過程TRACE_LEVEL引數中包含TRACE_LIMIT

1.3 建立儲存收集資料的表,以SYS使用者執行
?/rdbms/admin/tracetab.sql
?/rdbms/admin/dbmspbt.sql包含跟蹤型別宣告

1.4 跟蹤級別
跟蹤呼叫級別：
* Level 1: Trace all calls. This corresponds to the constant trace_all_calls.
* Level 2: Trace calls to enabled program units only. This corresponds to the constant trace_enabled_calls.
注：Enabling cannot be detected for remote procedure calls (RPCs); hence, RPCs are only traced with level 1.
無論呼叫程式還是被呼叫程式開啟跟蹤，則滿足開啟跟蹤條件。

跟蹤例外級別：
* Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.
* Level 2: Trace exceptions raised in enabled program units only. This corresponds to trace_enabled_exceptions.

跟蹤SQL級別：
* Level 1: Trace all SQL. This corresponds to the constant trace_all_sql.
* Level 2: Trace SQL in enabled program units only. This corresponds to the constant trace_enabled_sql.

跟蹤行級別：
* Level 1: Trace all lines. This corresponds to the constant trace_all_lines.
* Level 2: Trace lines in enabled program units only. This corresponds to the constant trace_enabled_lines.

2，測試
10g版本需要以SYS使用者執行?/rdbms/admin/tracetab.sql：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show user
USER is "SYS"

SQL> @?/rdbms/admin/tracetab.sql

--建立測試用儲存過程
create or replace procedure prc_test(p_num in number) as
  l_dummy number := 0;
begin
  for i in 1..p_num loop
     select l_dummy + 1 into l_dummy from dual;
  end loop;
end;
/

--以不同的跟蹤級別執行三次
BEGIN
  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
  prc_test(100);
  DBMS_TRACE.clear_plsql_trace;
  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
  prc_test(100);
  DBMS_TRACE.clear_plsql_trace;
  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
  prc_test(100);
  DBMS_TRACE.clear_plsql_trace;
END;
/

SQL> select runid,run_owner from sys.plsql_trace_runs;

     RUNID RUN_OWNER
---------- -------------------------------
         1 MH
         2 MH
         3 MH

set linesize 120
col runid for 999
col event_seq for 99999
col event_unit for a15
col event_unit_kind for a20
col event_line for 9999

--trace all call
select runid,event_seq,event_unit,event_unit_kind,event_line from sys.plsql_trace_events where runid=1;
RUNID EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND      EVENT_LINE
----- --------- --------------- -------------------- ----------
    1         1
    1         2
    1         3 DBMS_TRACE      PACKAGE BODY                 21
    1         4 DBMS_TRACE      PACKAGE BODY                 67
    1         5 DBMS_TRACE      PACKAGE BODY                 72
    1         6      ANONYMOUS BLOCK               3
    1         7 PRC_TEST        PROCEDURE                     7
    1         8      ANONYMOUS BLOCK               4
    1         9 DBMS_TRACE      PACKAGE BODY                 76
    1        10 DBMS_TRACE      PACKAGE BODY                 63
    1        11 DBMS_TRACE      PACKAGE BODY                 57
    1        12 DBMS_TRACE      PACKAGE BODY                 12
    1        13 DBMS_TRACE      PACKAGE BODY                 58
    1        14 DBMS_TRACE      PACKAGE BODY                 66
    1        15

--trace all sql
col event_comment for a30
select runid,event_seq,event_unit,event_unit_kind,event_line,event_comment from sys.plsql_trace_events where runid=2;
RUNID EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND      EVENT_LINE             EVENT_COMMENT
----- --------- --------------- -------------------- ---------- -------------------------
    2         1                                                 PL/SQL Trace Tool started
    2         2                                                 Trace flags changed
    2         3 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2         4 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2         5 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2         6 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
...
    2        98 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2        99 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2       100 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2       101 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2       102 PRC_TEST        PROCEDURE                     5 SELECT :B1 + 1 FROM DUAL
    2       103                                                 PL/SQL trace stopped

103 rows selected.

--trace all line
RUNID EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND      EVENT_LINE             EVENT_COMMENT
----- --------- --------------- -------------------- ---------- -------------------------
    3         1                                                 PL/SQL Trace Tool started
    3         2                                                 Trace flags changed
    3         3 DBMS_TRACE      PACKAGE BODY                 66 New line executed
    3         4 DBMS_TRACE      PACKAGE BODY                 67 New line executed
    3         5 DBMS_TRACE      PACKAGE BODY                 72 New line executed
    3         6      ANONYMOUS BLOCK               9 New line executed
    3         7 PRC_TEST        PROCEDURE                     2 New line executed
    3         8 PRC_TEST        PROCEDURE                     4 New line executed
    3         9 PRC_TEST        PROCEDURE                     5 New line executed
    3        10 PRC_TEST        PROCEDURE                     4 New line executed
    3        11 PRC_TEST        PROCEDURE                     5 New line executed
...
    3       206 PRC_TEST        PROCEDURE                     4 New line executed
    3       207 PRC_TEST        PROCEDURE                     5 New line executed
    3       208 PRC_TEST        PROCEDURE                     4 New line executed
    3       209 PRC_TEST        PROCEDURE                     7 New line executed
    3       210      ANONYMOUS BLOCK              10 New line executed
    3       211 DBMS_TRACE      PACKAGE BODY                 76 New line executed
    3       212 DBMS_TRACE      PACKAGE BODY                 63 New line executed
    3       213 DBMS_TRACE      PACKAGE BODY                 57 New line executed
    3       214 DBMS_TRACE      PACKAGE BODY                 12 New line executed
    3       215 DBMS_TRACE      PACKAGE BODY                 57 New line executed
    3       216 DBMS_TRACE      PACKAGE BODY                 58 New line executed
    3       217 DBMS_TRACE      PACKAGE BODY                 63 New line executed
    3       218 DBMS_TRACE      PACKAGE BODY                 66 New line executed
    3       219 DBMS_TRACE      PACKAGE BODY                 21 New line executed
    3       220                                                 PL/SQL trace stopped

220 rows selected.