大批量插入数据时禁用约束使用exceptions表找出违反约束的记录

偏执的太偏执、 2022-06-10 22:39 42阅读 0赞

使用exceptions表找出违反约束的记录演示：  
批量向表中插入大量数据时禁用约束，提高插入速度禁用约束约束禁用后导致违反约束的记录也插入到表中了，启用约束时会失败，这时我们要找出违反约束的记录更新或删除违反约束的记录  
1.创建一个测试表test  
create table test(id number(5),name varchar(10));  
SQL> create table test(id number(5),name varchar(10));  
表已创建。  
2.向表中插入两条记录  
insert into test values(1,'jack');  
insert into test values(2,'kebo');  
SQL> insert into test values(1,'jack');  
已创建 1 行。  
SQL> insert into test values(2,'kebo');  
已创建 1 行。  
3.为test表创建一个primary key名为id\_pk  
alter table test add constraint id\_pk primary key(id);  
4.然后插入数据  
insert into test values(2,'ddd') 这时会报错违反主键约束  
SQL> insert into test values(2,'ddd');  
insert into test values(2,'ddd')  
\*  
第 1 行出现错误:  
ORA-00001: 违反唯一约束条件 (HR.ID\_PK)  
SQL>

5.禁用约束  
alter table test disable constraint id\_pk;  
SQL> alter table test disable constraint id\_pk;  
表已更改。  
6.然后再次插入  
insert into test values(2,'ddd') 这是语句可以插入进去  
SQL> insert into test values(2,'ddd');  
已创建 1 行。  
SQL> commit;  
提交完成。  
7.使用exceptions表找出违反约束的记录  
创建exceptions表，执行建表脚本oracle自己提供的脚本@?\\rdbms\\admin\\utlexcpt.sql  
SQL> @?\\rdbms\\admin\\utlexcpt.sql;  
表已创建。  
SQL>  
8.执行如下语句把违反约束的记录插入到exceptions表中  
alter table test enable constraint id\_pk exceptions into exceptions;  
SQL> alter table test enable constraint id\_pk exceptions into exceptions;  
alter table test enable constraint id\_pk exceptions into exceptions  
\*  
第 1 行出现错误:  
ORA-02437: 无法验证 (HR.ID\_PK) - 违反主键 \--这里报错是正常的

SQL> select \* from exceptions;

ROW\_ID             OWNER  
\------------------ -------------------------------------------------------  
TABLE\_NAME  
\------------------------------------------------------------  
CONSTRAINT  
\------------------------------------------------------------  
AAAR/iAAEAAAAGrAAC HR  
TEST  
ID\_PK

AAAR/iAAEAAAAGrAAB HR  
TEST  
9.查询test表中违反约束的记录  
select rowid,id,name from test where rowid in (select row\_id from exceptions);  
SQL> select rowid,id,name from test where rowid in (select row\_id from exceptions);

ROWID                      ID NAME  
\------------------ ---------- --------------------  
AAAR/iAAEAAAAGrAAB          2 kebo  
AAAR/iAAEAAAAGrAAC          2 ddd  
10.把违反的约束记录更改一下  
update test set id=3 where rowid='AAAR/iAAEAAAAGrAAC';  
SQL> update test set id=3 where rowid='AAAR/iAAEAAAAGrAAC';  
已更新 1 行。  
11.启用约束  
alter table test enable constraint id\_pk;  
SQL> alter table test enable constraint id\_pk;  
表已更改。