目录
一、问题现象
使用数据泵expdp导出1个schema,名称是mes_prd,有个表NEIP_TASK,主键ID是触发器自增的id,impdp导入测试库测试时,发现表里的数据比自增序列的值要大,导致插入数据报错。
二、问题分析
1、分析原因:
由于数据库先进行序列导出,然后再进行表数据导出。然后在导出的过程中,该表一直有插入操作,最终导致了这种差异。
比如:
先导出序列时,序列号是19000010,此时一直有数据插入到表NEIP_TASK,也就是序列是不断增加的; 再导出表数据,可能此时表NEIP_TASK的ID(自增序列)变为了19000020; 最后impdp时,导入的序列值时19000010,而表中的ID时19000020,那么等下次插入数据时,触发器产生的序列时19000011,而表NEIP_TASK中已经有找个ID了,因为是主键,所以导致插入报错。
三、解决方案
重建触发器中的序列,让序列的开始值为表主键最大值+1。
四、案例重现
1、准备测试环境
需要建立测试表,序列,触发器和模拟业务插入数据的存储过程。
(1)建立测试表
drop table test purge; create table test (test_id number(20) primary key,name varchar2(20));
(2)创建序列
drop sequence test_seq; create sequence test_seq start with 1 increment by 1;
(3)创建触发器
create or replace trigger test_trigger before insert on test for each row begin select test_seq.nextval into :new.test_id from dual; end ; /
(4)创建实现循环添加数据的存储过程
--存储过程中使用需要显示赋权:grant execute on dbms_lock to mes_prd; create or replace procedure proc_insert_test is begin loop insert into test(name) values ('LLL15'); commit; dbms_lock.sleep(2); end loop; end; /
2、开始模拟该表不断插入
由于我这里实际使用的是死循环,所以只要开始执行存储过程,每2秒都会向测试表插入1条测试数据,直到手工停止。
--执行该存储过程 exec proc_insert_test; --查询表的数量,确认是每2秒多一条数据 select count(*) from test;
3、进行数据泵导出操作
确认导出目录,编写expdp导出语句,最终将这个schema导出。实际命令如下:
SQL> create or replace directory backup_mes as '/backup'; SQL> GRANT READ,WRITE ON DIRECTORY backup_mes to system; [oracle@qdmes220:/home/oracle]$expdp system/oracle directory=backup_mes dumpfile=mesprd.dmp logfile=mesprd_expdp.log schemas=mes_prd
4、进行数据泵导入操作
(1)停止上述存储过程写入
(2)删除原来的表
SQL> conn mes_prd/123; SQL> drop table test;
(3)删除原来的序列
SQL> drop sequence test_seq;
(3)导入数据
[oracle@qdmes220:/home/oracle]$impdp system/oracle directory=backup_mes dumpfile=mesprd.dmp logfile=mesprd_impdp.log schemas=mes_prd
5、问题现象重现并解决
(1)查看当前表的序列
SQL> select max(test_id) from test;
MAX(TEST_ID) ------------ 722
(2)查看序列
必须先用nextval查询下一个序列的值之后,才可以用该语句查询当前序列值。
SQL> SELECT test_seq.nextval FROM DUAL; SQL> SELECT test_seq.currval FROM DUAL;
CURRVAL ---------- 681
(3)执行存储过程
SQL> exec proc_insert_test; 报错:唯一性约束问题
(4)解决:读取表中的序列最大值,将序列产生值+1
重新创建序列的语句如下:
--重新创建序列 SQL> drop sequence test_seq; SQL> create sequence test_seq start with 723 increment by 1;
五、重新查看多表中,序列最新值大于表的自增主键,并统一解决
excel表已处理过程下载:
https://www.topunix.com/wp-content/uploads/2022/10/mes-rman-sequence.xlsx
1、查看用户有效序列
一般等于1的是没事用过的,所以我们过滤这些。
SQL> select sequence_name,last_number from dba_sequences where SEQUENCE_OWNER='MES_PRD' and last_number!=1 order by last_number desc;
2、找到对应的表触发的触发器
一般通过触发器进行触发,给表添加自增主键,都是通过triggering_event='insert',trigger_type='BEFORE EACH ROW'。通过如下筛选。
(1)查询满足条件的触发器
set linesize 300 set pagesize 100000 col table_name for a40 col trigger_body for a60 select trigger_name,triggering_event,trigger_type,table_name,trigger_body from user_triggers where status='ENABLED' and triggering_event='INSERT' and trigger_type='BEFORE EACH ROW';
(2)利用sqlplus 执行上述触发器
因为trigger_body是long类型,无法直接通过字符串函数进行筛选,可以通过上述查出的内容。
10:07:25 MES_PRD@mesorcl>select table_name,trigger_body from user_triggers where status='ENABLED' and triggering_event='INSERT' and trigger_type='BEFORE EACH ROW'; TABLE_NAME TRIGGER_BODY ---------------------------------------- ------------------------------------------------------------ GDLPIA begin select SEQ_GDLPIA_ID.nextval into :new.PIA01 from dual; end trg_GDLPIA_Ins GDLPIB begin select SEQ_GDLPIB_ID.nextval into :new.PIB01 from dual; end trg_GDLPIB_Ins
(3)利用excel(方方格子的删除空行,空格功能)+txt的替换功能
--利用excel+方方格子分离为这种
TABLE_NAME TRIGGER_BODY ---------------------------------------- ------------------------------------------------------------ GDLPIA select SEQ_GDLPIA_ID.nextval into :new.PIA01 from dual; GDLPIB select SEQ_GDLPIB_ID.nextval into :new.PIB01 from dual; ...
--利用txt的tab和空格等替换功能改为如下格式
TABLE_NAME SEQUENCE_NAME GDLPIA SEQ_GDLPIA_ID GDLPIB SEQ_GDLPIB_ID ...
3、获取对应的主键值及序列值
(1)利用excel获取的sql语句, 完成所有表自增主键列最大值的输出
select max(PIA01) from GDLPIA ; select max(PIB01) from GDLPIB ; ...
(2)利用excel获取的SQL语句,完成sequence所有序列last_number值并降序输出
select sequence_name,last_number from user_sequences order by last_number desc;
(3)对比1和2,将表字段最大值>序列的进行记录,后面进行序列重建
4、重建序列
(1)删除比较出的序列值小于表字段最大值的序列
drop sequence SEQ_SO_LOG_ID ; drop sequence SEQ_MES_JMT_DELIVERSHEETS_ID; drop sequence SEQ_HRCQC_ID;
(2)重新创建序列,序列开始值比表字段最大值大1即可。
create sequence SEQ_SO_LOG_ID start with 3480208 increment by 1; create sequence SEQ_MES_JMT_DELIVERSHEETS_ID start with 2449547 increment by 1; create sequence SEQ_HRCQC_ID start with 940130 increment by 1;
5、执行相关业务,查看序列值及数据表最大值变化
(1)更改后的序列值
select * from user_sequences where sequence_name='SEQ_SO_LOG_ID ' or sequence_name='SEQ_MES_JMT_DELIVERSHEETS_ID ' or sequence_name='SEQ_HRCQC_ID' order by last_number desc;
(2)序列值的变化
select SEQ_MES_JMT_MATERIALDETAILS_ID.nextval from dual; select SEQ_DC_TASK.nextval from dual; select SEQ_DC_MSGREQ.nextval from dual;
(3)数据表最大值变化
select max(sysLogCode) from SO_LOG; select max(id) from MES_JMT_DELIVERSHEETS; select max(CQC01) from HRCQC;