可以使用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]
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
1 7 PRC_TEST PROCEDURE 7
1 8
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
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
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-755353/ ，如需转载，请注明出处，否则将追究法律责任。
