如何跟踪 SQL 触发器?

Question

我想跟踪触发器的执行，但我不知道该怎么做。设置自动跟踪后，我想到的第一个想法是强制触发，但它仍然不起作用。我应该如何查看触发器的执行跟踪器？

Answer 1

Oracle 为您提供了两种跟踪 PL/SQL 的好方法。 首选方法是使用 DBMS_HPROF 包。 不太好(它更老(是DBMS_PROFILER。

由于DBMS_PROFILER更简单，而您的要求也很简单，因此我将演示这一点。

首先，让我们创建一个带有触发器的表：

CREATE TABLE matt_test1 ( a number );
CREATE TRIGGER matt_trg1 BEFORE INSERT ON matt_test1 FOR EACH ROW
BEGIN
NULL;
END;

接下来，我们启动探查器，运行INSERT(应触发触发器(，然后停止探查器：

EXEC DBMS_PROFILER.START_PROFILER;
INSERT INTO matt_test1 (a) values (1);
EXEC DBMS_PROFILER.STOP_PROFILER;

现在，有更好的方法来启动和停止探查器。 这些方法将返回已使用的运行 ID，你将需要该 ID 才能查询结果。 在我们的快速而肮脏的示例中，我们将只查看最新的探查器结果：

SELECT * FROM plsql_profiler_runs order by runid desc;

在我的系统上，那是runid2。

接下来，我们查询PLSQL_PROFILER*表以获取有关运行期间执行的 PL/SQL 的所有信息。 这不会只列出触发器 - 它会更多：几乎从PL/SQL调用的任何SQL语句以及从PL/SQL调用的任何其他PL/SQL块(过程或函数(。

这是我曾经用于此的查询(在我切换到为此使用DBMS_HPROF之前(：

SELECT   d.runid,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time / POWER (10, 9) total_seconds,
d.min_time / POWER (10, 9) min_seconds,
d.max_time / POWER (10, 9) max_seconds,
ss.source line_text,
SUM (d.total_time / POWER (10, 9)) OVER (PARTITION BY NULL ORDER BY d.total_time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                                         cumulative_seconds,
SUM (d.total_time / POWER (10, 9)) OVER (PARTITION BY NULL) elapsed_seconds_for_run
FROM     plsql_profiler_data d INNER JOIN plsql_profiler_units u ON u.runid = d.runid
AND                                                       u.unit_number = d.unit_number
LEFT JOIN SYS.user$ su ON su.NAME = u.unit_owner
LEFT JOIN SYS.obj$ so
ON  so.NAME = u.unit_name
AND so.owner# = su.user#
AND DECODE (so.type#,
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
'UNDEFINED') = u.unit_type
LEFT JOIN SYS.source$ ss ON ss.obj# = so.obj#
AND                        ss.line = d.line#
WHERE    1=1
and d.runid = 2
AND      d.total_occur > 0
ORDER BY d.total_time DESC;

最后，结果：

+-------+-----------------+-------------+-------------+-------+-------------+---------------+-------------+-------------+-----------+--------------------+-------------------------+
| RUNID |    UNIT_TYPE    | UNIT_OWNER  |  UNIT_NAME  | LINE# | TOTAL_OCCUR | TOTAL_SECONDS | MIN_SECONDS | MAX_SECONDS | LINE_TEXT | CUMULATIVE_SECONDS | ELAPSED_SECONDS_FOR_RUN |
+-------+-----------------+-------------+-------------+-------+-------------+---------------+-------------+-------------+-----------+--------------------+-------------------------+
|     2 | ANONYMOUS BLOCK | <anonymous> | <anonymous> |     1 |           2 |   0.000010001 |    0.000001 | 0.000008001 |           |        0.000010001 |             0.000013001 |
|     2 | TRIGGER         | APPS        | MATT_TRG1   |     2 |           1 |      0.000002 |    0.000002 |    0.000002 | BEGIN     |        0.000012001 |             0.000013001 |
|     2 | ANONYMOUS BLOCK | <anonymous> | <anonymous> |     1 |           1 |      0.000001 |    0.000001 |    0.000001 |           |        0.000013001 |             0.000013001 |
+-------+-----------------+-------------+-------------+-------+-------------+---------------+-------------+-------------+-----------+--------------------+-------------------------+

您可以看到我们的触发器在上面的第 #2 行中触发。

Answer 2

我想到两种方式：

如果可以更改触发器代码，请创建日志表并从触发器正文向其写入调试消息：

CREATE TABLE trigger_log (t TIMESTAMP, m VARCHAR2(4000));
CREATE OR REPLACE my_trigger BEFORE INSERT ...
BEGIN 
INSERT INTO trigger_log(t, m)
VALUES (systimestamp,'my_trigger fired :new_id is='||:new_id);
... rest of trigger body ...
END;
/

其次，如果可以更改审核设置，请为相关表打开审核。