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操作終止和回滾.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1193464/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DML ERROR LOGGING總結Error
- bulk insert 在mssql中使用SQL
- Inserting Data with DML Error Logging（轉）Error
- oracle bulk Insert 比insert 快不是一點啊.Oracle
- Bulk Insert命令具體
- sqlserver bulk insert報錯Cannot bulk load because the file could not be opened.SQLServer
- 生產庫故障分析（主外來鍵和DML效能分析）
- 11g append 和常規insert在logging FORCE_LOGGING產生redo量差異APP
- SQL Server Bulk Insert批量資料匯入SQLServer
- 使用Bulk Collect提高Oracle查詢效率Oracle
- Oracle 11g GoldenGate單向複製配置（DML）OracleGo
- Oracle dataguard報錯：Error 1017 received logging on to the standbyOracleError
- Oracle goldengate 11g （二）【DML and DDL單向複製】OracleGo
- ORACLE 11g新特性-允許DDL鎖等待DML鎖Oracle
- Oracle 的 bulk collect用法Oracle
- 原創：oracle DML介紹與使用Oracle
- Oracle Parallel DMLOracleParallel
- oracle 11g expdb autoextend tablespace and create directory error;OracleError
- 避免使用Oracle的char型別Oracle型別
- 程式設計師如何避免故障？程式設計師
- Elasticsearch的Bulk API使用ElasticsearchAPI
- 使用oracle bulk collect 提高select FETCH LOOP的速度OracleOOP
- Oracle中的insert/insert all/insert firstOracle
- Oracle分批提交DMLOracle
- 使用dbms_parallel_execute來完成DML的並行Parallel並行
- oracle bulk collects 之 java selectOracleJava
- Oracle® Database Error Messages 11g Release 2 (11.2)OracleDatabaseError
- Oracle 的 INSERT ALL和INSERT FIRSTOracle
- Oracle Insert BLOBOracle
- 轉載--oracle DML鎖Oracle
- 使用Logminer來分析具體的DML操作日誌
- 【故障處理】Bug : ASM FAILS WITH CHECKRESOURCE ERROR ERROR CODE = 139ASMAIError
- 故障分析 | 從 Insert 併發死鎖分析 Insert 加鎖原始碼邏輯原始碼
- RAID 磁碟故障input/output errorAIError
- Oracle 使用一條insert語句完成多表插入Oracle
- 【故障】“ORACLE使用者被鎖定”故障處理和分析Oracle
- 使用 RxJs Observable 來避免 Angular 應用中的 Promise 使用JSAngularPromise
- ORACLE insert命令解析Oracle