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

eric0435發表於2014-06-25

使用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操作終止和回滾.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-1193464/,如需轉載,請註明出處,否則將追究法律責任。

相關文章