今天同事提出了一个问题:
使用数据泵expdp导出1个schema,有个表主键是触发器自增的id,导入测试库测试时,发现表里的数据比自增序列的值要大。导致插入数据报错。
最终结论是:
由于数据库先进行序列导出,然后再进行表数据导出。然后在导出的过程中,该表一直有插入操作,最终导致了这种差异。
解决方法:
重建触发器中的序列,让序列的开始值为表主键最大值+1。
下面我构造实验完整演示下这种场景。
1.准备测试环境
需要建立测试表,序列,触发器和模拟业务插入数据的存储过程。
以下是实际的创建语句:
--在测试用户jingyu下创建测试表book2drop table book2 purge;create table book2( bookId number(10) primary key, name varchar2(20) ); --创建序列 drop sequence book2_seq; create sequence book2_seq start with 1 increment by 1; --创建触发器 create or replace trigger book2_trigger before insert on book2 for each row begin select book2_seq.nextval into :new.bookId from dual; end ; / --创建实现循环添加数据的存储过程/* --存储过程中使用需要显示赋权 grant execute on dbms_lock to jingyu; */create or replace procedure proc_insert_book2 isbegin loop insert into book2(name) values ('xx'); commit; dbms_lock.sleep(1); end loop;end; /
2.开始模拟该表不断插入
由于我这里实际使用的是死循环,所以只要开始执行存储过程,每秒都会向测试表插入1条测试数据,直到手工停止。
--执行该存储过程exec proc_insert_book2;--查询表的数量,确认是每秒多一条数据select count(*) from book2;
3.进行数据泵导出操作
确认导出目录,编写expdp导出语句,最终将jingyu这个schema导出。实际命令如下:
--expdp 导出create or replace directory jy as '/opt/app/orabak/'; expdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp schemas=jingyu
实际执行导出的输出如下:
[oracle@jyrac1 orabak]$ expdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp schemas=jingyuExport: Release 11.2.0.4.0 - Production on Thu Jun 8 17:08:29 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsStarting "JINGYU"."SYS_EXPORT_SCHEMA_05": jingyu/******** directory=jy dumpfile=jingyu.dmp schemas=jingyu Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 10.12 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "JINGYU"."T2" 6.649 MB 100000 rows . . exported "JINGYU"."SYS_EXPORT_SCHEMA_01" 142.0 KB 1195 rows . . exported "JINGYU"."SYS_EXPORT_SCHEMA_02" 142.2 KB 1196 rows . . exported "JINGYU"."SYS_EXPORT_SCHEMA_03" 142.6 KB 1198 rows . . exported "JINGYU"."SYS_EXPORT_SCHEMA_04" 149.7 KB 1201 rows . . exported "JINGYU"."T_OLD" 160.8 KB 20000 rows . . exported "JINGYU"."T" 82.94 KB 10000 rows . . exported "JINGYU"."T_NOLOG" 51.53 KB 5998 rows . . exported "JINGYU"."BOOK" 5.421 KB 2 rows . . exported "JINGYU"."BOOK2" 6.734 KB 123 rows . . exported "JINGYU"."EMP" 8.562 KB 14 rows . . exported "JINGYU"."T1" 11.75 KB 100 rowsMaster table "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded ******************************************************************************Dump file set for JINGYU.SYS_EXPORT_SCHEMA_05 is: /opt/app/orabak/jingyu.dmpJob "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully completed at Thu Jun 8 17:10:26 2017 elapsed 0 00:01:36
4.进行数据泵导入操作
将上一步的导出文件,导入到另一个新建的测试用户jingyu2下。实际命令如下:
--创建测试用户并赋予一定的权限create user jingyu2 identified by jingyu2 default tablespace dbs_d_jingyu;grant connect, resource to jingyu2;--impdp 导入到用户jingyu2impdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2
实际执行导入的输出如下:
[oracle@jyrac1 orabak]$ impdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2Import: Release 11.2.0.4.0 - Production on Thu Jun 8 17:11:21 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table "JINGYU"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "JINGYU"."SYS_IMPORT_FULL_01": jingyu/******** directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2 Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"JINGYU2" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "JINGYU2"."T2" 6.649 MB 100000 rows . . imported "JINGYU2"."SYS_EXPORT_SCHEMA_01" 142.0 KB 1195 rows . . imported "JINGYU2"."SYS_EXPORT_SCHEMA_02" 142.2 KB 1196 rows . . imported "JINGYU2"."SYS_EXPORT_SCHEMA_03" 142.6 KB 1198 rows . . imported "JINGYU2"."SYS_EXPORT_SCHEMA_04" 149.7 KB 1201 rows . . imported "JINGYU2"."T_OLD" 160.8 KB 20000 rows . . imported "JINGYU2"."T" 82.94 KB 10000 rows . . imported "JINGYU2"."T_NOLOG" 51.53 KB 5998 rows . . imported "JINGYU2"."BOOK" 5.421 KB 2 rows . . imported "JINGYU2"."BOOK2" 6.734 KB 123 rows . . imported "JINGYU2"."EMP" 8.562 KB 14 rows . . imported "JINGYU2"."T1" 11.75 KB 100 rowsProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PRO_SELECT" created with compilation warningsORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PROC_INSERT_BOOK2" created with compilation warningsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "JINGYU"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Thu Jun 8 17:11:52 2017 elapsed 0 00:00:26
导入完成,但存在一些警告,与本实验有关的只有"JINGYU2"."PROC_INSERT_BOOK2" 编辑警告需要处理,在下面的步骤中详细说明。
5.问题现象重现并解决
问题现象重现:
查询到表最大的BOOKID大于序列的当前值,具体情况如下:
SQL> select max(BOOKID) from book2; MAX(BOOKID) ----------- 505SQL> select book2_seq.currval from dual;select book2_seq.currval from dual * ERROR at line 1: ORA-08002: sequence BOOK2_SEQ.CURRVAL is not yet defined in this session SQL> select book2_seq.nextval from dual; NEXTVAL ---------- 341
导入的存储过程存在编译警告的问题,排查原因是权限问题,需要先处理下:
--执行存储过程报错对象无效 SQL> exec proc_insert_book2 BEGIN proc_insert_book2; END; *ERROR at line 1: ORA-06550: line 1, column 7: PLS-00905: object JINGYU2.PROC_INSERT_BOOK2 is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored --重新编译存储过程依然有错误 SQL> alter procedure proc_insert_book2 compile; Warning: Procedure altered with compilation errors. --显示具体的错误 SQL> show errors Errors for PROCEDURE PROC_INSERT_BOOK2: LINE/COL ERROR-------- -----------------------------------------------------------------6/5 PL/SQL: Statement ignored6/5 PLS-00201: identifier 'DBMS_LOCK' must be declared--根据错误提示,赋权解决 SQL> show user USER is "SYS"SQL> grant execute on dbms_lock to jingyu2; Grant succeeded. --再次编译成功 SQL> alter procedure proc_insert_book2 compile; Procedure altered.
编译存储过程成功后,执行它模拟插入数据,意料之中的会报错:
SQL> exec proc_insert_book2 BEGIN proc_insert_book2; END; *ERROR at line 1: ORA-00001: unique constraint (JINGYU2.SYS_C0011351) violated ORA-06512: at "JINGYU2.PROC_INSERT_BOOK2", line 4ORA-06512: at line 1--查询测试表主键bookid的最大值 SQL> select max(bookid) from book2; MAX(BOOKID) ----------- 505
重新创建序列,序列开始值设置为MAX(BOOKID)+1,再次执行就可以正常插入了。
重新创建序列的语句如下:
--重新创建序列 drop sequence book2_seq; create sequence book2_seq start with 506 increment by 1;
至此,整个实验完成。
AlfredZhao?版权所有「从Oracle起航,领略精彩的IT技术。」
http://www.cnblogs.com/jyzhao/p/6964924.html