在Oracle数据库运维和开发过程中，对表执行DML操作时经常会遇到各类错误，比如违反唯一约束、字段值超出长度限制、数据类型不匹配等，这些错误会导致DML操作失败，默认情况下只能获取到简单的报错提示，不利于后续的问题排查和错误数据修复。实现DML操作的错误记录日志，能够把错误的详细信息、操作时间、操作语句等内容持久化保存，方便后续分析。
Oracle记录DML错误日志的两种常用方案
方案一：使用DBMS_ERRLOG包自动创建错误日志表
Oracle提供了内置的DBMS_ERRLOG包，可以快速为指定的表创建对应的错误日志表，在DML操作时通过LOG ERRORS子句将错误信息自动写入该日志表，无需手动编写复杂的异常处理逻辑。
1. 创建错误日志表
使用DBMS_ERRLOG.CREATE_ERROR_LOG存储过程为目标表创建错误日志表，语法如下：
-- 为目标表test_table创建错误日志表，日志表名为err_test_table
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(
dml_table_name => 'test_table', -- 目标表名，大小写敏感，建议大写
err_log_table_name => 'err_test_table', -- 错误日志表名，可选，默认是ERR_+目标表名
err_log_table_owner => 'SCOTT', -- 错误日志表的所属用户，可选
skip_unsupported => TRUE -- 是否跳过不支持的列类型，可选
);
END;
/创建完成后，错误日志表会包含错误发生的ora_err_number$错误编号、ora_err_mesg$错误信息、ora_err_rowid$行ID、ora_err_optyp$操作类型（I插入、U更新、D删除）等字段，同时还会包含目标表的所有字段，用于存储错误操作对应的行数据。
2. DML操作时记录错误日志
在执行插入、更新、删除操作时，添加LOG ERRORS子句即可将错误自动写入日志表，示例代码如下：
-- 插入数据，错误记录到err_test_table表，最多记录100条错误
INSERT INTO test_table (id, name, age)
SELECT 1, '张三', 25 FROM DUAL
UNION ALL
SELECT 1, '李四', 30 FROM DUAL -- id重复，违反唯一约束
LOG ERRORS INTO err_test_table ('insert_test') REJECT LIMIT 100;
-- 更新数据，错误记录到错误日志表
UPDATE test_table
SET age = 200 -- 假设age字段有检查约束，最大值100
WHERE id = 1
LOG ERRORS INTO err_test_table ('update_test') REJECT LIMIT UNLIMITED;
-- 删除数据，错误记录到错误日志表
DELETE FROM test_table
WHERE id = 999 -- 假设行不存在，部分场景可能触发错误
LOG ERRORS INTO err_test_table ('delete_test') REJECT LIMIT 10;其中REJECT LIMIT用于指定允许的最大错误数量，达到该数量后操作会停止，UNLIMITED表示不限制错误数量，所有错误都会记录。
方案二：自定义错误日志表结合PL/SQL异常处理
如果需要更灵活地记录错误内容，比如额外记录操作人、操作时间、完整SQL语句等信息，可以自定义错误日志表，通过PL/SQL的异常处理块捕获DML错误并写入日志表。
1. 创建自定义错误日志表
-- 创建自定义错误日志表
CREATE TABLE custom_dml_err_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 日志ID，自增
table_name VARCHAR2(50) NOT NULL, -- 操作的表名
dml_type VARCHAR2(10) NOT NULL, -- DML类型：INSERT、UPDATE、DELETE
err_code NUMBER, -- 错误编号
err_msg VARCHAR2(2000), -- 错误信息
err_data CLOB, -- 错误对应的操作数据
operate_time DATE DEFAULT SYSDATE, -- 操作时间
operator VARCHAR2(50) -- 操作人
);2. 编写带异常处理的DML操作存储过程
CREATE OR REPLACE PROCEDURE proc_insert_test_data (
p_id IN NUMBER,
p_name IN VARCHAR2,
p_age IN NUMBER,
p_operator IN VARCHAR2
) AS
v_err_code NUMBER;
v_err_msg VARCHAR2(2000);
BEGIN
-- 执行插入操作
INSERT INTO test_table (id, name, age)
VALUES (p_id, p_name, p_age);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- 捕获错误信息
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
-- 将错误信息写入自定义日志表
INSERT INTO custom_dml_err_log (
table_name, dml_type, err_code, err_msg, err_data, operator
) VALUES (
'TEST_TABLE',
'INSERT',
v_err_code,
v_err_msg,
'ID:' || p_id || ',NAME:' || p_name || ',AGE:' || p_age,
p_operator
);
COMMIT;
-- 可以选择是否重新抛出异常
RAISE;
END proc_insert_test_data;
/两种方案的对比与选择
| 对比维度 | DBMS_ERRLOG方案 | 自定义日志表方案 |
|---|---|---|
| 实现复杂度 | 低，几行代码即可完成 | 高，需要自定义表、编写存储过程和异常处理逻辑 |
| 灵活性 | 低，日志字段固定，无法扩展 | 高，可自定义任意字段，记录额外信息 |
| 适用场景 | 简单的错误记录需求，仅需保留错误基础信息 | 复杂的业务场景，需要记录操作人、完整数据、操作时间等扩展信息 |
| 性能影响 | 低，Oracle内置优化 | 略高，需要额外执行日志插入逻辑 |
注意事项
- 使用
DBMS_ERRLOG方案时，目标表不能是远程表、不支持的字段类型（如LONG、BFILE等）会被跳过，需要提前确认表结构是否支持。 - 错误日志表需要定期清理，避免长期积累大量无用数据占用存储空间。
- 自定义日志表方案时，异常处理块中写入日志的操作也需要做异常捕获，避免日志写入失败导致主业务操作也失败。
- 生产环境中建议对错误日志表建立合适的索引，比如操作时间、表名字段，提升后续查询效率。