OK, so I was rebuilding a 15 billion row table (looong story, mostly being rebuilt due to poor initial design). At any rate, some of the data was "bad" (dupes we didn't know we had). I found out after the fact that 10g has a "log errors into" clause for all kinds of DML statements. Cool! I just wish I had known *before* I started the rebuild ...

See below for a test case...

SQL> create table t0808(c number primary key);

Table created.

SQL> begin
2 for i in 1..5 loop
3 insert into t0808 values(i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_errlog.create_error_log('T0808');

PL/SQL procedure successfully completed.

SQL> insert into t0808 select rownum from dba_users
2 log errors into err$_t0808 reject limit unlimited;

17 rows created.

SQL> commit;

Commit complete.

SQL> select * from t0808;

C
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

22 rows selected.

SQL> COLUMN ora_err_mesg$ FORMAT A50
SQL> SELECT ora_err_mesg$ from err$_t0808;

ORA_ERR_MESG$
----------------------------------------------------------------------
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated
ORA-00001: unique constraint (REP.SYS_C005175) violated

5 rows selected.

SQL>


Pretty cool, eh?