目录
一、问题现象
使用数据泵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;
