oracle 11g使用DML Error Logging来避免bulk insert故障

使用DML Error Logging来避免bulk insert故障
当使用带有子查询的insert语句来加载数据时如果出现错误.系统会终止该语句并回滚整个操作.这是非常消耗时间和资源的操作.如果insert这样的语句可以使用DML Error Logging功能来避免这种情况.

为了使用DML error logging功能需要增加一个子句来指定error logging表名来记录当DML操作所遇到的错误记录.当向insert
语句增加一个error logging子句时特定类型的错误将不会被终止和回滚语句.相反每一个错误记录会被记录到表中操作语句能继续执行.在操作完成后可以对错误记录执行修正操作.

DML error logging功能可以与insert,update,merge和delete语句一起使用.

为了使用DML error logging功能来插入数据
1.来创建一个error logging表
可以手工创建或者使用dbms_errlog包来自动创建.

2.执行一个包含error logging子句的insert语句:
.可以选择引用自己创建的error logging表.如果没有提供一个error logging表表名.数据库会使用一个缺省的error logging表.
缺省的error loggin表名为err$_后面紧接着是插入表名的前25个字符.

.可以选择包含一个标签(一个数字或者有括号的字符串)它将添加到错误日志中来帮助你识别哪个语句产生的错误.如果标签被忽略将使用null值

.可以选择包含reject limit子句
这个子句指示在insert语句终止和回滚之前可以遇到的错误的最大数量.可以指定为unlimited.缺省的reject limit为0,这意味着
在遇到第一个错误时会记录错误并回滚语句.对于并行操作reject limit应用到每一个并行服务器进程.

3.查询error logging表对于产生错误的行执行修正.

error logging表的格式
一个error logging表由两部分组成:
.描述错误的一组强制列.例如一个列包含了oracle的错误代码

.包含造成错误行数据的一组可选列.这些列名与执行插入操作的表中的列名相匹配.
error logging表中的这部分的列数可以是0,1或者多个直到与DML表中的列数相等.如果在error logging表中的列与DML表中的列
有相同的名字,那么违反插入操作的行记录中的相关数据会被写入这个error logging表中的列中.如果DML表中的列在error logging表中没有与之相关的列,那么这个列将不会记录.如果error logging表包含一个与DML表不相匹配的列那么这个列会被忽略.

因为类型转换错误是一种可能出现的错误类型,在error logging表中的可选列的数据类型必须是能捕获任何值而不会丢失数据或转换错误的数据类型.(如果可选日志列与DML表列有相同的数据类型,那么记录捕获问题数据时也会有相同的数据转换问题).数据库为了记录造成转换错误的数据的有用信息做出了最大努力.如果值不能通过派生得到,对于这个列会记录null值.插入errog logging表的一个错误会导致语句终止.

表:强制错误描述列

--------------------------------------------------------------------------------------------------------------
列名                            数据类型              描述
--------------------------------------------------------------------------------------------------------------
ora_err_number$                 number                oracle错误代码
ora_err_mesg$                   varchar2(2000)        oracle错误消息文本
ora_err_rowid$                  rowid                 错误行的rowid(对于更新和删除)
ora_err_optyp$                  varchar2(2)           操作类型:insert(i),update(u),delete(d)
                                                      注意:来自merge操作的update子句和insert子句的错误
                                                      通过U,I来区分
ora_err_tag$                    varchar2(2000)        提供给error logging子句的标签值
--------------------------------------------------------------------------------------------------------------

表:错误日志表的列数据类型

--------------------------------------------------------------------------------------------------------------
DML表列类型          错误日志表的列类型             注意
--------------------------------------------------------------------------------------------------------------
number               varchar2(4000)                 能够记录转换错误
char/varchar2(n)     varchar2(4000)                 记录没有信息丢失的任何值
nchar/nvarchar2(n)   nvarchar2(4000)                记录没有信息丢失的任何值
date/timestamp       varchar2(4000)                 记录没有信息丢失的任何值.使用缺省的date/time格式来转换成
                                                    字符格式
raw                  raw(2000)                      记录没有信息丢失的任何值
rowid                urowid                         记录任何类型的rowid
long/lob                                            不支持
用户定义数据类型                                    不支持
--------------------------------------------------------------------------------------------------------------

创建错误日志表
可以手工创建一个错误日志表或者使用pl/sql包来自动创建

使用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.

查看错误信息

SQL> select * from err_emp;
 
ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                 ORA_ERR_ROWID$   ORA_ERR_OPTYP$ ORA_ERR_TAG$    EMPLOYEE_ID   FIRST_NAME         LAST_NAME            EMAIL                PHONE_NUMBER         HIRE_DATE        JOB_ID           SALARY       COMMISSION_PCT     MANAGER_ID     DEPARTMENT_ID
--------------- ------------------------------------------------------------- ---------------- -------------- --------------- ------------- ------------------ -------------------- -------------------- -------------------- ---------------- ---------------- ------------ ------------------ -------------- ------------------
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load       198           Donald             OConnell             DOCONNEL             650.507.9833         21-JUN-07        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      198           Donald             OConnell             DOCONNEL             650.507.9833         21-JUN-07        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      199           Douglas            Grant                DGRANT               650.507.9844         13-JAN-08        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      200           Jennifer           Whalen               JWHALEN              515.123.4444         17-SEP-03        AD_ASST          4400                            101            10
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      201           Michael            Hartstein            MHARTSTE             515.123.5555         17-FEB-04        MK_MAN           13000                           100            20
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      202           Pat                Fay                  PFAY                 603.123.6666         17-AUG-05        MK_REP           6000                            201            20
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      203           Susan              Mavris               SMAVRIS              515.123.7777         07-JUN-02        HR_REP           6500                            101            40
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      204           Hermann            Baer                 HBAER                515.123.8888         07-JUN-02        PR_REP           10000                           101            70
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      205           Shelley            Higgins              SHIGGINS             515.123.8080         07-JUN-02        AC_MGR           12008                           101            110
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      206           William            Gietz                WGIETZ               515.123.8181         07-JUN-02        AC_ACCOUNT       8300                            205            110
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      100           Steven             King                 SKING                515.123.4567         17-JUN-03        AD_PRES          24000                                          90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      101           Neena              Kochhar              NKOCHHAR             515.123.4568         21-SEP-05        AD_VP            17000                           100            90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      102           Lex                De Haan              LDEHAAN              515.123.4569         13-JAN-01        AD_VP            17000                           100            90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      103           Alexander          Hunold               AHUNOLD              590.423.4567         03-JAN-06        IT_PROG          9000                            102            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      104           Bruce              Ernst                BERNST               590.423.4568         21-MAY-07        IT_PROG          6000                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      105           David              Austin               DAUSTIN              590.423.4569         25-JUN-05        IT_PROG          4800                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      106           Valli              Pataballa            VPATABAL             590.423.4560         05-FEB-06        IT_PROG          4800                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      107           Diana              Lorentz              DLORENTZ             590.423.5567         07-FEB-07        IT_PROG          4200                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      108           Nancy              Greenberg            NGREENBE             515.124.4569         17-AUG-02        FI_MGR           12008                           101            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      109           Daniel             Faviet               DFAVIET              515.124.4169         16-AUG-02        FI_ACCOUNT       9000                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      110           John               Chen                 JCHEN                515.124.4269         28-SEP-05        FI_ACCOUNT       8200                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      111           Ismael             Sciarra              ISCIARRA             515.124.4369         30-SEP-05        FI_ACCOUNT       7700                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      112           Jose Manuel        Urman                JMURMAN              515.124.4469         07-MAR-06        FI_ACCOUNT       7800                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      113           Luis               Popp                 LPOPP                515.124.4567         07-DEC-07        FI_ACCOUNT       6900                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      114           Den                Raphaely             DRAPHEAL             515.127.4561         07-DEC-02        PU_MAN           11000                           100            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      115           Alexander          Khoo                 AKHOO                515.127.4562         18-MAY-03        PU_CLERK         3100                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      116           Shelli             Baida                SBAIDA               515.127.4563         24-DEC-05        PU_CLERK         2900                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      117           Sigal              Tobias               STOBIAS              515.127.4564         24-JUL-05        PU_CLERK         2800                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      118           Guy                Himuro               GHIMURO              515.127.4565         15-NOV-06        PU_CLERK         2600                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      119           Karen              Colmenares           KCOLMENA             515.127.4566         10-AUG-07        PU_CLERK         2500                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      120           Matthew            Weiss                MWEISS               650.123.1234         18-JUL-04        ST_MAN           8000                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      121           Adam               Fripp                AFRIPP               650.123.2234         10-APR-05        ST_MAN           8200                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      122           Payam              Kaufling             PKAUFLIN             650.123.3234         01-MAY-03        ST_MAN           7900                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      123           Shanta             Vollman              SVOLLMAN             650.123.4234         10-OCT-05        ST_MAN           6500                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      124           Kevin              Mourgos              KMOURGOS             650.123.5234         16-NOV-07        ST_MAN           5800                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      125           Julia              Nayer                JNAYER               650.124.1214         16-JUL-05        ST_CLERK         3200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      126           Irene              Mikkilineni          IMIKKILI             650.124.1224         28-SEP-06        ST_CLERK         2700                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      127           James              Landry               JLANDRY              650.124.1334         14-JAN-07        ST_CLERK         2400                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      128           Steven             Markle               SMARKLE              650.124.1434         08-MAR-08        ST_CLERK         2200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      129           Laura              Bissot               LBISSOT              650.124.5234         20-AUG-05        ST_CLERK         3300                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      130           Mozhe              Atkinson             MATKINSO             650.124.6234         30-OCT-05        ST_CLERK         2800                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      131           James              Marlow               JAMRLOW              650.124.7234         16-FEB-05        ST_CLERK         2500                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      132           TJ                 Olson                TJOLSON              650.124.8234         10-APR-07        ST_CLERK         2100                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      133           Jason              Mallin               JMALLIN              650.127.1934         14-JUN-04        ST_CLERK         3300                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      134           Michael            Rogers               MROGERS              650.127.1834         26-AUG-06        ST_CLERK         2900                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      135           Ki                 Gee                  KGEE                 650.127.1734         12-DEC-07        ST_CLERK         2400                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      136           Hazel              Philtanker           HPHILTAN             650.127.1634         06-FEB-08        ST_CLERK         2200                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      137           Renske             Ladwig               RLADWIG              650.121.1234         14-JUL-03        ST_CLERK         3600                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      138           Stephen            Stiles               SSTILES              650.121.2034         26-OCT-05        ST_CLERK         3200                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      139           John               Seo                  JSEO                 650.121.2019         12-FEB-06        ST_CLERK         2700                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      140           Joshua             Patel                JPATEL               650.121.1834         06-APR-06        ST_CLERK         2500                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      141           Trenna             Rajs                 TRAJS                650.121.8009         17-OCT-03        ST_CLERK         3500                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      142           Curtis             Davies               CDAVIES              650.121.2994         29-JAN-05        ST_CLERK         3100                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      143           Randall            Matos                RMATOS               650.121.2874         15-MAR-06        ST_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      144           Peter              Vargas               PVARGAS              650.121.2004         09-JUL-06        ST_CLERK         2500                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      145           John               Russell              JRUSSEL              011.44.1344.429268   01-OCT-04        SA_MAN           14000        .4                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      146           Karen              Partners             KPARTNER             011.44.1344.467268   05-JAN-05        SA_MAN           13500        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      147           Alberto            Errazuriz            AERRAZUR             011.44.1344.429278   10-MAR-05        SA_MAN           12000        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      148           Gerald             Cambrault            GCAMBRAU             011.44.1344.619268   15-OCT-07        SA_MAN           11000        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      149           Eleni              Zlotkey              EZLOTKEY             011.44.1344.429018   29-JAN-08        SA_MAN           10500        .2                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      150           Peter              Tucker               PTUCKER              011.44.1344.129268   30-JAN-05        SA_REP           10000        .3                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      151           David              Bernstein            DBERNSTE             011.44.1344.345268   24-MAR-05        SA_REP           9500         .25                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      152           Peter              Hall                 PHALL                011.44.1344.478968   20-AUG-05        SA_REP           9000         .25                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      153           Christopher        Olsen                COLSEN               011.44.1344.498718   30-MAR-06        SA_REP           8000         .2                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      154           Nanette            Cambrault            NCAMBRAU             011.44.1344.987668   09-DEC-06        SA_REP           7500         .2                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      155           Oliver             Tuvault              OTUVAULT             011.44.1344.486508   23-NOV-07        SA_REP           7000         .15                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      156           Janette            King                 JKING                011.44.1345.429268   30-JAN-04        SA_REP           10000        .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      157           Patrick            Sully                PSULLY               011.44.1345.929268   04-MAR-04        SA_REP           9500         .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      158           Allan              McEwen               AMCEWEN              011.44.1345.829268   01-AUG-04        SA_REP           9000         .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      159           Lindsey            Smith                LSMITH               011.44.1345.729268   10-MAR-05        SA_REP           8000         .3                 146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      160           Louise             Doran                LDORAN               011.44.1345.629268   15-DEC-05        SA_REP           7500         .3                 146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      161           Sarath             Sewall               SSEWALL              011.44.1345.529268   03-NOV-06        SA_REP           7000         .25                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      162           Clara              Vishney              CVISHNEY             011.44.1346.129268   11-NOV-05        SA_REP           10500        .25                147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      163           Danielle           Greene               DGREENE              011.44.1346.229268   19-MAR-07        SA_REP           9500         .15                147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      164           Mattea             Marvins              MMARVINS             011.44.1346.329268   24-JAN-08        SA_REP           7200         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      165           David              Lee                  DLEE                 011.44.1346.529268   23-FEB-08        SA_REP           6800         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      166           Sundar             Ande                 SANDE                011.44.1346.629268   24-MAR-08        SA_REP           6400         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      167           Amit               Banda                ABANDA               011.44.1346.729268   21-APR-08        SA_REP           6200         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      168           Lisa               Ozer                 LOZER                011.44.1343.929268   11-MAR-05        SA_REP           11500        .25                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      169           Harrison           Bloom                HBLOOM               011.44.1343.829268   23-MAR-06        SA_REP           10000        .2                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      170           Tayler             Fox                  TFOX                 011.44.1343.729268   24-JAN-06        SA_REP           9600         .2                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      171           William            Smith                WSMITH               011.44.1343.629268   23-FEB-07        SA_REP           7400         .15                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      172           Elizabeth          Bates                EBATES               011.44.1343.529268   24-MAR-07        SA_REP           7300         .15                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      173           Sundita            Kumar                SKUMAR               011.44.1343.329268   21-APR-08        SA_REP           6100         .1                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      174           Ellen              Abel                 EABEL                011.44.1644.429267   11-MAY-04        SA_REP           11000        .3                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      175           Alyssa             Hutton               AHUTTON              011.44.1644.429266   19-MAR-05        SA_REP           8800         .25                149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      176           Jonathon           Taylor               JTAYLOR              011.44.1644.429265   24-MAR-06        SA_REP           8600         .2                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      177           Jack               Livingston           JLIVINGS             011.44.1644.429264   23-APR-06        SA_REP           8400         .2                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      178           Kimberely          Grant                KGRANT               011.44.1644.429263   24-MAY-07        SA_REP           7000         .15                149            
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      179           Charles            Johnson              CJOHNSON             011.44.1644.429262   04-JAN-08        SA_REP           6200         .1                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      180           Winston            Taylor               WTAYLOR              650.507.9876         24-JAN-06        SH_CLERK         3200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      181           Jean               Fleaur               JFLEAUR              650.507.9877         23-FEB-06        SH_CLERK         3100                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      182           Martha             Sullivan             MSULLIVA             650.507.9878         21-JUN-07        SH_CLERK         2500                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      183           Girard             Geoni                GGEONI               650.507.9879         03-FEB-08        SH_CLERK         2800                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      184           Nandita            Sarchand             NSARCHAN             650.509.1876         27-JAN-04        SH_CLERK         4200                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      185           Alexis             Bull                 ABULL                650.509.2876         20-FEB-05        SH_CLERK         4100                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      186           Julia              Dellinger            JDELLING             650.509.3876         24-JUN-06        SH_CLERK         3400                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      187           Anthony            Cabrio               ACABRIO              650.509.4876         07-FEB-07        SH_CLERK         3000                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      188           Kelly              Chung                KCHUNG               650.505.1876         14-JUN-05        SH_CLERK         3800                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      189           Jennifer           Dilly                JDILLY               650.505.2876         13-AUG-05        SH_CLERK         3600                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      190           Timothy            Gates                TGATES               650.505.3876         11-JUL-06        SH_CLERK         2900                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      191           Randall            Perkins              RPERKINS             650.505.4876         19-DEC-07        SH_CLERK         2500                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      192           Sarah              Bell                 SBELL                650.501.1876         04-FEB-04        SH_CLERK         4000                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      193           Britney            Everett              BEVERETT             650.501.2876         03-MAR-05        SH_CLERK         3900                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      194           Samuel             McCain               SMCCAIN              650.501.3876         01-JUL-06        SH_CLERK         3200                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      195           Vance              Jones                VJONES               650.501.4876         17-MAR-07        SH_CLERK         2800                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      196           Alana              Walsh                AWALSH               650.507.9811         24-APR-06        SH_CLERK         3100                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      197           Kevin              Feeney               KFEENEY              650.507.9822         23-MAY-06        SH_CLERK         3000                            124            50
 
108 rows selected
 

从err_emp表中的记录可以看到不能插入test_emp的记录的错误信息是因为违反主键约束.

error logging的限制和警告
当执行DML操作时出现以下错误时oracle数据库会进行记录:
.列的值太长
.违反约束(not null,unique,referential,check)
.在触发器执行时触发的错误
.在子查询列与DML表相关列之间类型转换的错误
.分区映射错误
.特定的merge操作错误(ora-30926:Unable to get a stable set of rows for MERGE operation)

有一些错误不会记录且会造成DML操作终止和回滚.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.