用 APPEND 批量加载数据时，可避免不合格数据

批量加载数据在数据仓库里经常用到的方法，但最烦人的就是，加载几千万数据，而表快要扫描完的时候或扫到一半，突然给你来一个约束上的不符合，数据要rollback，就Table Scan 加 rollback 这会消费很时间。 

在10gR2  开始，加入了 LOG ERRORS 功能，可以把这情况去掉。用/*+APPEND*/ 批量来加载数据时，不合格的数据可另外传到错误表，之前加载的数据依然被加载，而还不会要求rollback，这功能跟SQL*Loader类似。

详细例子如下：

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 19 16:50:50 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> GRANT EXECUTE ON DBMS_ERRLOG TO scott; -- 调用包权限

Grant succeeded.

SQL> conn scott/tiger;
Connected.
SQL> CREATE TABLE tar_emp AS SELECT * FROM emp WHERE 1=2;

Table created.

SQL> alter table tar_emp add constraint CK_SCOTT_TAR_EMP_SAL check (SAL > 1000);
Table altered.

SQL> INSERT /*+APPEND*/ INTO tar_emp SELECT * FROM emp;
INSERT /*+APPEND*/ INTO tar_emp SELECT * FROM emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_SCOTT_TAR_EMP_SAL) violated

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TAR_EMP');

PL/SQL procedure successfully completed.

-- Oracle 会自动创建一个 "err$_ + 表名" 的表来保存批量加载时有错误的数据。
SQL> desc err$_tar_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)
 EMPNO                                              VARCHAR2(4000)
 ENAME                                              VARCHAR2(4000)
 JOB                                                VARCHAR2(4000)
 MGR                                                VARCHAR2(4000)
 HIREDATE                                           VARCHAR2(4000)
 SAL                                                VARCHAR2(4000)
 COMM                                               VARCHAR2(4000)
 DEPTNO                                             VARCHAR2(4000)

SQL> INSERT /*+APPEND*/ INTO tar_emp SELECT * FROM emp LOG ERRORS REJECT LIMIT UNLIMITED;

12 rows created.

SQL> commit;

Commit complete.

好了，加载完毕，分别看看两个表：
首先是目标表：

SQL >   SELECT   *   FROM  tar_emp;
1      7499     ALLEN    SALESMAN     7698      1981 - 2 - 20      1600.00      300.00      30
2      7521     WARD    SALESMAN     7698      1981 - 2 - 22      1250.00      500.00      30
3      7566     JONES    MANAGER     7839      1981 - 4 - 2      2975.00          20
4      7654     MARTIN    SALESMAN     7698      1981 - 9 - 28      1250.00      1400.00      30
5      7698     BLAKE    MANAGER     7839      1981 - 5 - 1      2850.00          30
6      7782     CLARK    MANAGER     7839      1981 - 6 - 9      2450.00          10
7      7788     SCOTT    ANALYST     7566      1987 - 4 - 19      3000.00          20
8      7839     KING    PRESIDENT         1981 - 11 - 17      5000.00          10
9      7844     TURNER    SALESMAN     7698      1981 - 9 - 8      1500.00      0.00      30
10      7876     ADAMS    CLERK     7788      1987 - 5 - 23      1100.00          20
11      7902     FORD    ANALYST     7566      1981 - 12 - 3      3000.00          20
12      7934     MILLER    CLERK     7782      1982 - 1 - 23      1300.00          10

然后是记录错误的表：

SQL >   SELECT  ora_err_number$, ora_err_mesg$, empno, sal  FROM  ERR$_TAR_EMP;
2290     "ORA - 02290 :  check   constraint  (SCOTT.CK_SCOTT_TAR_EMP_SAL) violated"     7369   800
2290     "ORA - 02290 :  check   constraint  (SCOTT.CK_SCOTT_TAR_EMP_SAL) violated"     7900   950

测试结束的后期工作：

SQL >   DROP   TABLE  err$_tar_emp;

Table  dropped.

SQL >   DROP   TABLE  tar_emp;
Table  dropped.

SQL >  conn  /   as  sysdba
Connected.
SQL >   REVOKE   EXECUTE   ON  DBMS_ERRLOG  FROM  scott;

Revoke  succeeded.