在 Oracle 中记录信息
本文介绍了在 Oracle 数据库中记录操作信息时可以使用的技术，例如自治事务和操作信息。
引言
虽然在使用数据库时可以在客户端以多种方式进行日志记录，但有时在数据库端记录信息也很有用。例如，在执行存储过程、运行批处理等时，了解执行了哪些步骤很重要。本文介绍了构建日志记录机制时需要了解的几项有用内容：自治事务以及模块/操作信息。
设置测试环境
首先，我们需要几个表。一个用于添加数据，另一个用于存储日志信息。测试表非常简单
-------------------------------------------------
-- Definition for Test table
-------------------------------------------------
CREATE TABLE Test (
SomeColumn VARCHAR2(30)
);只有一个列，用于放入一些文本。
日志表如下所示
-------------------------------------------------
-- Definition for LogEntry table
-------------------------------------------------
CREATE TABLE LogEntry (
Created DATE DEFAULT SYSDATE CONSTRAINT nn_LogEntry_Created NOT NULL,
CreatedBy VARCHAR2(30) DEFAULT USER CONSTRAINT nn_LogEntry_CreatedBy NOT NULL,
Sid NUMBER CONSTRAINT nn_LogEntry_Sid NOT NULL,
OsUser VARCHAR(20) CONSTRAINT nn_LogEntry_OsUser NOT NULL,
Machine VARCHAR(20) CONSTRAINT nn_LogEntry_Machine NOT NULL,
Process VARCHAR(12) CONSTRAINT nn_LogEntry_Process NOT NULL,
Program VARCHAR(100) CONSTRAINT nn_LogEntry_Program NOT NULL,
Module VARCHAR(100),
Action VARCHAR(100),
Text VARCHAR2(2000) CONSTRAINT nn_LogEntry_Text NOT NULL
); 其想法是将以下信息存储到 LogEntry 表中
Created，创建日志条目的时间。CreatedBy，添加日志条目的用户名。Sid，插入日志条目的会话的会话标识符。OsUser，实际的操作系统用户。Machine，语句执行所在的计算机名称。Process，语句执行的进程标识符。Program，语句执行的程序。Module，关于模块的描述性信息。Action，关于操作的描述性信息。Text，实际的日志条目文本。
日志记录包
为了实际将行插入 LogEntry 表，我创建了一个名为 Logging 的包。该包的定义如下。
-------------------------------------------------
-- Package definition for Logging
-------------------------------------------------
CREATE OR REPLACE PACKAGE Logging AUTHID DEFINER
AS
-- Writes a single entry to LogEntry-table
PROCEDURE LogEntry (vText varchar2);
-- Writes a single entry to LogEntry-table using autonomous transaction
PROCEDURE LogEntryAutonomous (vText varchar2);
END logging;
/ 让我们关注第一个过程。该过程如下（主体部分定义）：
-----------------------------------------------------------------------
-- Writes a single entry to LogEntry-table
-----------------------------------------------------------------------
PROCEDURE LogEntry (vText varchar2) AS
BEGIN
INSERT INTO LogEntry (
Sid,
OsUser,
Machine,
Process,
Program,
Module,
Action,
Text)
SELECT vs.Sid,
vs.OsUser,
vs.Machine,
vs.Process,
vs.Program,
vs.Module,
vs.Action,
vText
FROM v$session vs
WHERE sid = sys_context('USERENV','SID');
END LogEntry;
该过程将传递给它的文本插入到 LogEntry 表中（多么大的惊喜：）。但其中也存储了 v$session 视图的一些信息。这些信息有助于跟踪日志条目，了解哪个操作来自哪个用户或哪个工作站，操作是否来自同一会话等。
通常，用户没有权限在过程中使用 v$session。因此，必须授予拥有该包的用户对底层视图的 select 权限。以下语句授予必要的权限，必须使用 SYSDBA 权限运行。
GRANT SELECT ON sys.v_$session TO <user name goes here>;
/ 另外，为了从正确的会话获取会话信息，使用了 sys_context 来从 USERENV 命名空间中获取 SID。
简单测试
添加数据并提交
现在，让我们做一个小测试，看看一切是否正常。
--------------------------------------------
-- Simple test
--------------------------------------------
-- Add a log entry
EXECUTE Logging.LogEntry('Entering test 1');
-- Add a test row
INSERT INTO Test VALUES ('Test row 1');
-- Add a log entry
EXECUTE Logging.LogEntry('Test 1 ended');
-- Commit the transaction
COMMIT; 该脚本添加一个日志条目，插入一行，添加第二个日志条目，然后提交事务。如果我们查看 Test 表中的数据，它看起来像：
-- Select the data from the test table
SELECT * FROM Test; SOMECOLUMN
----------
Test row 1 而 LogEntry 表中的数据则如预期的那样：
-- Select the data from the log table
SELECT TO_CHAR(le.Created, 'dd.mm.yyyy hh24.mi.ss') as Created,
le.Sid,
le.OsUser,
le.Machine,
le.Process,
le.Program,
le.Text
FROM LogEntry le
ORDER BY le.Created DESC;结果：
CREATED SID OSUSER MACHINE PROCESS PROGRAM TEXT
------------------- --- ------ ------- ------- ------------- ---------------
29.04.2012 23.00.12 94 mika MyBox 2056 SQL Developer Test 1 ended
29.04.2012 23.00.10 94 mika MyBox 2056 SQL Developer Entering test 1 一切都如预期。
添加数据并回滚操作
让我们再做一个测试：
--------------------------------------------
-- Test inside a single transaction
--------------------------------------------
-- Add a log entry
EXECUTE Logging.LogEntry('Entering test 2');
-- Add a test row
INSERT INTO Test VALUES ('Test row 2');
-- Add a log entry
EXECUTE Logging.LogEntry('Test 2 ended');
-- Roll back the transaction
ROLLBACK; 基本上，这与第一个测试相同，只是这次事务没有提交而是回滚了。如果我们查看 Test 表中的数据，什么也没有改变（如预期）。
-- Select the data from the test table
SELECT * FROM Test; SOMECOLUMN
----------
Test row 1 现在，如果我们列出 LogEntry 表的内容，我们会发现此表也没有任何更改。
-- Select the data from the log table
SELECT TO_CHAR(le.Created, 'dd.mm.yyyy hh24.mi.ss') as Created,
le.Sid,
le.OsUser,
le.Machine,
le.Process,
le.Program,
le.Text
FROM LogEntry le
ORDER BY le.Created DESC;结果：
CREATED SID OSUSER MACHINE PROCESS PROGRAM TEXT
------------------- --- ------ ------- ------- ------------- ---------------
29.04.2012 23.00.12 94 mika MyBox 2056 SQL Developer Test 1 ended
29.04.2012 23.00.10 94 mika MyBox 2056 SQL Developer Entering test 1 但是，即使操作已回滚，了解已执行了一些操作也会很有用。例如，如果回滚是因为出了问题，我们可以记录一条关于情况的日志，但所有有趣的信息都会因为回滚而丢失。
使用自治事务
首先，什么是自治事务？自治事务不应与嵌套事务混淆，它们是完全不同的。嵌套事务共享与外部事务相同的上下文，而自治事务则不然。也许最简单的方法是将其视为一个并行、独立的事务。
当调用自治事务时，会离开原始事务上下文并开始一个“新”上下文。当此事务结束时，执行将返回到原始事务。这意味着：
- 如果自治事务执行提交，则自治事务中的修改数据会立即对所有事务可见。
- 两个事务不共享任何锁。
- 即使实际事务执行回滚，自治事务中已提交的数据仍然保留。
-----------------------------------------------------------------------
-- Writes a single entry to LogEntry-table using autonomous transaction
-----------------------------------------------------------------------
PROCEDURE LogEntryAutonomous (vText varchar2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
LogEntry(vText);
COMMIT;
END LogEntryAutonomous; 基本上，这个过程只是调用了前面定义的过程，但有两点需要注意。
PRAGMA AUTONOMOUS_TRANSACTION，此 pragma 定义该过程使用自治事务。其范围是整个过程。
另请注意过程末尾的 commit。尽管第一个过程没有执行提交或回滚，而是将决定权留给了过程的调用者，但现在必须在返回到实际事务之前在自治块内做出决定。
如果在块的末尾未提交或回滚事务，则会引发 ORA-06519 错误。此错误的解释是：
06519. 00000 - "active autonomous transaction detected and rolled back"
*Cause: Before returning from an autonomous PL/SQL block, all autonomous
transactions started within the block must be completed (either
committed or rolled back). If not, the active autonomous
transaction is implicitly rolled back and this error is raised.
*Action: Ensure that before returning from an autonomous PL/SQL block,
any active autonomous transactions are explicitly committed
or rolled back. 现在让我们运行一个测试。
--------------------------------------------
-- Test using autonomous transaction
--------------------------------------------
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Entering test 3');
-- Add a test row
INSERT INTO Test VALUES ('Test row 3');
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Test 3 ended');
-- Roll back the transaction
ROLLBACK; 与之前一样，执行了一些修改并将事务回滚。测试表中的数据保持不变，但 LogEntry 表中的数据如何？
-- Select the data from the log table
SELECT TO_CHAR(le.Created, 'dd.mm.yyyy hh24.mi.ss') as Created,
le.Sid,
le.OsUser,
le.Machine,
le.Process,
le.Program,
le.Text
FROM LogEntry le
ORDER BY le.Created DESC; 数据现在如下所示：
CREATED SID OSUSER MACHINE PROCESS PROGRAM TEXT
------------------- --- ------ ------- ------- ------------- ---------------
29.04.2012 23.46.26 94 mika MyBox 2056 SQL Developer Test 3 ended
29.04.2012 23.46.24 94 mika MyBox 2056 SQL Developer Entering test 3
29.04.2012 23.00.12 94 mika MyBox 2056 SQL Developer Test 1 ended
29.04.2012 23.00.10 94 mika MyBox 2056 SQL Developer Entering test 1 因此，即使事务被回滚，现在日志条目也会显示出来，因为自治事务已被提交。添加额外信息
前面的示例从客户端收集了一些有用的信息。但是，如果日志条目是从存储过程等记录的，则仅了解调用程序或会话可能不足够。
Oracle 有一个 DBMS_APPLICATION_INFO 包，可用于从调用客户端设置更多信息。在本文的范围内，有趣的过程是：
SET_MODULE，设置当前模块和操作。SET_ACTION，设置当前操作。
通过使用这些过程，客户端应用程序可以设置更多关于客户端上下文的信息。
一个使用 DBMS_APPLICATION_INFO 的小测试：
--------------------------------------------
-- Setting module and action with autonomous logging
--------------------------------------------
-- Set module and action
EXECUTE DBMS_APPLICATION_INFO.SET_MODULE('Test module', 'First action');
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Entering test 4');
-- Set action
EXECUTE DBMS_APPLICATION_INFO.SET_ACTION('Some insert sequence');
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Insert row 4.1');
-- Add a test row
INSERT INTO Test VALUES ('Test row 4.1');
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Insert row 4.2');
-- Add a test row
INSERT INTO Test VALUES ('Test row 4.2');
-- Add a log entry
EXECUTE Logging.LogEntryAutonomous('Test 4 ended');
-- Roll back the transaction
ROLLBACK;首先设置模块和初始操作，然后创建日志条目。在此之后，设置一个新操作，并在该操作中运行几个插入。最后，添加一个日志条目，并回滚事务。
运行上述脚本后，使用以下命令获取 LogEntry 中的数据：
-- Select the data from the log table
SELECT TO_CHAR(le.Created, 'dd.mm.yyyy hh24.mi.ss') as Created,
le.Sid,
le.Program,
le.Module,
le.Action,
le.Text
FROM LogEntry le
ORDER BY le.Created DESC; 结果如下。最后的 4 行来自此批处理，之前的行来自之前的批处理。
CREATED SID PROGRAM MODULE ACTION TEXT
------------------- --- ------------- ----------- -------------------- ---------------
30.04.2012 00.01.44 94 SQL Developer Test module Some insert sequence Test 4 ended
30.04.2012 00.01.41 94 SQL Developer Test module Some insert sequence Insert row 4.2
30.04.2012 00.01.37 94 SQL Developer Test module Some insert sequence Insert row 4.1
30.04.2012 00.01.35 94 SQL Developer Test module First action Entering test 4
29.04.2012 23.46.26 94 SQL Developer Test module Some insert Test 3 ended
29.04.2012 23.46.24 94 SQL Developer Test module Some insert Entering test 3
29.04.2012 23.00.12 94 SQL Developer Test module Some insert Test 1 ended
29.04.2012 23.00.10 94 SQL Developer Test module Some insert Entering test 1 结语
自治事务在多种情况下都非常方便。日志记录就是其中之一。首次使用它们时，请记住它们不是嵌套事务，并且行为不像嵌套事务。
此外，正确使用模块和操作信息，可以从数据库端更密切地监控执行上下文。这提供了许多有用的信息，尤其是在解决问题时。
历史
- 2012年4月30日：文章创建。