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