Thursday, May 3, 2012

Dml error logging

Dml error loggin is around since Oracle version 10.2.0.1
What is it.
One time datawarehouse was running a job that was inserting more then 10 milion rows.
Almost at the end the bacth job failed with ORA-00001: unique constraint violatedThat let to some frustration because of the error message no rows where inserted.As so many time Oracle has a soltion for this.

Here is a little example

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);

CREATE TABLE t2 (col1 NUMBER PRIMARY KEY);

INSERT INTO t1 VALUES(1);

INSERT INTO t1 VALUES(2);

INSERT INTO t1 VALUES(3);

INSERT INTO t1 VALUES(4);

INSERT INTO t2 VALUES(1);

INSERT INTO t2 VALUES(2);

INSERT INTO t2 VALUES(3);

INSERT INTO t2 VALUES(8);

INSERT INTO t2 VALUES(9);

COMMIT;

and here is the trickSQL>EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('t1',error_log_t1')

this means we put errorlogging on for table t1.and created the errorlog table error_log_t1Now we going to insert rows into table t1 from table t2SQL>insert into t1 select * from t2 log errors reject limit unlimited

2 rows created

That should have been 5 rows created.
Why where te rows rejected
select * from error_log_t1