今天同事提出了一個(gè)問題：
使用數(shù)據(jù)泵expdp導(dǎo)出1個(gè)schema，有個(gè)表主鍵是觸發(fā)器自增的id，導(dǎo)入測試庫測試時(shí)，發(fā)現(xiàn)表里的數(shù)據(jù)比自增序列的值要大。導(dǎo)致插入數(shù)據(jù)報(bào)錯(cuò)。
最終結(jié)論是：
由于數(shù)據(jù)庫先進(jìn)行序列導(dǎo)出，然后再進(jìn)行表數(shù)據(jù)導(dǎo)出。然后在導(dǎo)出的過程中，該表一直有插入操作，最終導(dǎo)致了這種差異。
解決方法：
重建觸發(fā)器中的序列，讓序列的開始值為表主鍵最大值+1。

下面我構(gòu)造實(shí)驗(yàn)完整演示下這種場景。

1.準(zhǔn)備測試環(huán)境

需要建立測試表，序列，觸發(fā)器和模擬業(yè)務(wù)插入數(shù)據(jù)的存儲過程。
以下是實(shí)際的創(chuàng)建語句：

--在測試用戶jingyu下創(chuàng)建測試表book2drop table book2 purge;create table book2(       
   bookId number(10) primary key,   
   name varchar2(20)         
); 

--創(chuàng)建序列  drop sequence book2_seq;    
create sequence book2_seq start with 1 increment by 1;    
  
--創(chuàng)建觸發(fā)器      create or replace trigger book2_trigger       
before insert on book2     
for each row       begin       select book2_seq.nextval into :new.bookId from dual;      
end ;  
/ 

--創(chuàng)建實(shí)現(xiàn)循環(huán)添加數(shù)據(jù)的存儲過程/*
--存儲過程中使用需要顯示賦權(quán)
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.開始模擬該表不斷插入

由于我這里實(shí)際使用的是死循環(huán)，所以只要開始執(zhí)行存儲過程，每秒都會向測試表插入1條測試數(shù)據(jù)，直到手工停止。

--執(zhí)行該存儲過程exec proc_insert_book2;--查詢表的數(shù)量，確認(rèn)是每秒多一條數(shù)據(jù)select count(*) from book2;

3.進(jìn)行數(shù)據(jù)泵導(dǎo)出操作

確認(rèn)導(dǎo)出目錄，編寫expdp導(dǎo)出語句，最終將jingyu這個(gè)schema導(dǎo)出。實(shí)際命令如下：

--expdp 導(dǎo)出create or replace directory jy as '/opt/app/orabak/';
expdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp schemas=jingyu

實(shí)際執(zhí)行導(dǎo)出的輸出如下：

[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.進(jìn)行數(shù)據(jù)泵導(dǎo)入操作

將上一步的導(dǎo)出文件，導(dǎo)入到另一個(gè)新建的測試用戶jingyu2下。實(shí)際命令如下：

--創(chuàng)建測試用戶并賦予一定的權(quán)限create user jingyu2 identified by jingyu2 default tablespace dbs_d_jingyu;grant connect, resource to jingyu2;--impdp 導(dǎo)入到用戶jingyu2impdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2

實(shí)際執(zhí)行導(dǎo)入的輸出如下：

[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

導(dǎo)入完成，但存在一些警告，與本實(shí)驗(yàn)有關(guān)的只有"JINGYU2"."PROC_INSERT_BOOK2" 編輯警告需要處理，在下面的步驟中詳細(xì)說明。

5.問題現(xiàn)象重現(xiàn)并解決

問題現(xiàn)象重現(xiàn)：
查詢到表最大的BOOKID大于序列的當(dāng)前值，具體情況如下：

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

導(dǎo)入的存儲過程存在編譯警告的問題，排查原因是權(quán)限問題，需要先處理下：

--執(zhí)行存儲過程報(bào)錯(cuò)對象無效
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

--重新編譯存儲過程依然有錯(cuò)誤
SQL> alter procedure proc_insert_book2 compile;

Warning: Procedure altered with compilation errors.

--顯示具體的錯(cuò)誤
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--根據(jù)錯(cuò)誤提示，賦權(quán)解決
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.

編譯存儲過程成功后，執(zhí)行它模擬插入數(shù)據(jù)，意料之中的會報(bào)錯(cuò)：

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

重新創(chuàng)建序列，序列開始值設(shè)置為MAX(BOOKID)+1，再次執(zhí)行就可以正常插入了。
重新創(chuàng)建序列的語句如下：

--重新創(chuàng)建序列  drop sequence book2_seq;    
create sequence book2_seq start with 506 increment by 1;

至此，整個(gè)實(shí)驗(yàn)完成。

AlfredZhao?版權(quán)所有「從Oracle起航，領(lǐng)略精彩的IT技術(shù)?！?/p>

http://www.cnblogs.com/jyzhao/p/6964924.html