Oracle 11g使用DML Error Logging来避免bulk insert故障(二)

使用dbms_errlog包可以自动创建一个错误日志表.create_error_log过程将创建一个有所有强制描述错误的列加上DML表中所有列
的一个错误日志表
首先创建一个要存储数据的表test_emp
SQL> create table test_emp as select * from hr.employees where 1=2;

Table created.

SQL> alter table test_emp add primary key (employee_id);

Table altered.

先向test_temp表中插入一条记录因为让后面的插入操作产生违反主键约束的错误
SQL> insert into test_emp select * from hr.employees where rownum<2;

1 row created.

SQL> commit;

创建错误日志表
SQL> execute dbms_errlog.create_error_log('TEST_EMP','ERR_EMP');

PL/SQL procedure successfully completed.

SQL> desc err_emp
Name Null Type
----------------------------------------- -------- ----------------------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPLOYEE_ID VARCHAR2(4000)
FIRST_NAME VARCHAR2(4000)
LAST_NAME VARCHAR2(4000)
EMAIL VARCHAR2(4000)
PHONE_NUMBER VARCHAR2(4000)
HIRE_DATE VARCHAR2(4000)
JOB_ID VARCHAR2(4000)
SALARY VARCHAR2(4000)
COMMISSION_PCT VARCHAR2(4000)
MANAGER_ID VARCHAR2(4000)
DEPARTMENT_ID VARCHAR2(4000)

执行插入操作
SQL> insert into test_emp select * from hr.employees log errors into err_emp('test_load1') reject limit 40;

106 rows created.

SQL> insert into test_emp select * from hr.employees log errors into err_emp('test_load1') reject limit 1000;

0 rows created.

SQL> commit;

Commit complete.