DML Error Logging in Oracle 10g

DML Error Logging in Oracle 10g

主要在于使用DBMS_ERRLOG.create_error_log 这个包来跟踪dml错误信息

SQL> CREATE TABLE source (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10),
4 description VARCHAR2(50),
5 CONSTRAINT source_pk PRIMARY KEY (id)
6 );

表已创建。

SQL> DECLARE
2 TYPE t_tab IS TABLE OF source%ROWTYPE;
3 l_tab t_tab := t_tab();
4 BEGIN
5 FOR i IN 1 .. 100000 LOOP
6 l_tab.extend;
7 l_tab(l_tab.last).id := i;
8 l_tab(l_tab.last).code := TO_CHAR(i);
9 l_tab(l_tab.last).description := 'Description for ' || TO_CHAR(i);
10 END LOOP;
11
12 -- For a possible error condition.
13 l_tab(1000).code := NULL;
14 l_tab(10000).code := NULL;
15
16 FORALL i IN l_tab.first .. l_tab.last
17 INSERT INTO source VALUES l_tab(i);
18
19 COMMIT;
20 END;
21 /

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE dest (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10) NOT NULL,
4 description VARCHAR2(50),
5 CONSTRAINT dest_pk PRIMARY KEY (id)
6 );

表已创建。

SQL> CREATE TABLE dest_child (
2 id NUMBER,
3 dest_id NUMBER,
4 CONSTRAINT child_pk PRIMARY KEY (id),
5 CONSTRAINT dest_child_dest_fk FOREIGN KEY (dest_id) REFERENCES dest(id)
6 );

表已创建。

SQL> BEGIN
2 DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
3 END;
4 /

PL/SQL 过程已成功完成。

SQL> SELECT owner, table_name, tablespace_name
2 FROM all_tables
3 WHERE owner = 'TEST';

OWNER TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
TEST TEST
USERS

SQL> DESC err$_dest
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
ID VARCHAR2(4000)
CODE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)

SQL> INSERT INTO dest
2 SELECT *
3 FROM source;
SELECT *
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> INSERT INTO dest
2 SELECT *
3 FROM source
4 LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

已创建99998行。

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'INSERT';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10;
SET code = DECODE(id, 9, NULL, 10, NULL, code)
*
第 2 行出现错误:
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10
4 LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

已更新8行。

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'UPDATE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1407
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

1407
ORA-01407: 无法更新 ("SYS"."DEST"."CODE") 为 NULL

SQL> DELETE FROM dest
2 WHERE id > 50000;

已删除50000行。

SQL> MERGE INTO dest a
2 USING source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE SET a.code = b.code,
6 a.description = b.description
7 WHEN NOT MATCHED THEN
8 INSERT (id, code, description)
9 VALUES (b.id, b.code, b.description);
VALUES (b.id, b.code, b.description)
*
第 9 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> MERGE INTO dest a
2 USING source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE SET a.code = b.code,
6 a.description = b.description
7 WHEN NOT MATCHED THEN
8 INSERT (id, code, description)
9 VALUES (b.id, b.code, b.description)
10 LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 行已合并。

 

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'MERGE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------
1400
ORA-01400: 无法将 NULL 插入 ("SYS"."DEST"."CODE")

SQL> INSERT INTO dest_child (id, dest_id) VALUES (1, 100);

已创建 1 行。

SQL> INSERT INTO dest_child (id, dest_id) VALUES (2, 101);

已创建 1 行。

SQL> DELETE FROM dest;
DELETE FROM dest
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录

SQL> DELETE FROM dest
2 LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;

已删除99996行。

SQL> COLUMN ora_err_mesg$ FORMAT A69
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'DELETE';

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
---------------------------------------------------------------------
2292
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录

2292
ORA-02292: 违反完整约束条件 (SYS.DEST_CHILD_DEST_FK) - 已找到子记录