実験:Oracleデータ・ポンプエクスポートインポートのシーケンスの問題

10624 ワード

今日同僚は1つの問題を提出しました:データポンプexpdpを使って1つのschemaを導出して、1つのテーブルの主なキーはトリガの自己増加のidで、テストライブラリのテストを導入する時、表の中のデータは自己増加のシーケンスの値より大きいことを発見します.データの挿入エラーが発生しました.最終的な結論は、データベースがシーケンスエクスポートを行い、テーブルデータエクスポートを行うためです.その後、エクスポート中にテーブルが挿入され続け、最終的にこの違いが発生しました.解決方法:トリガ内のシーケンスを再構築し、シーケンスの開始値をテーブルプライマリ・キーの最大値+1にします.
次は実験を構築してこのシーンを完全に実証します.

1.テスト環境の準備

2.シミュレーションを開始

3.データポンプ導出動作

を行う.

4.データポンプの導入操作

を行う.

5.問題の再現と解決

1.試験環境の準備

テストテーブル、シーケンス、トリガ、およびシミュレーショントラフィック挿入データの格納プロセスを確立する必要があります.実際の作成文は次のとおりです.

-- jingyu book2
drop 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 is
begin
  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=jingyu

Export: Release 11.2.0.4.0 - Production on Thu Jun 8 17:08:29 2017

Copyright (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 Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "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_DATA
Total estimation using BLOCKS method: 10.12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing 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 rows
Master table "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded
******************************************************************************
Dump file set for JINGYU.SYS_EXPORT_SCHEMA_05 is:
  /opt/app/orabak/jingyu.dmp
Job "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully completed at Thu Jun 8 17:10:26 2017 elapsed 0 00:01:36

4.データポンプの導入操作を行う

前のエクスポートファイルを、別の新しいテストユーザーjingyu 2にインポートします.実際のコマンドは次のとおりです.

-- 
create user jingyu2 identified by jingyu2 default tablespace dbs_d_jingyu;
grant connect, resource to jingyu2;

--impdp  jingyu2
impdp 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:jingyu2

Import: Release 11.2.0.4.0 - Production on Thu Jun 8 17:11:21 2017

Copyright (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 Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "JINGYU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JINGYU"."SYS_IMPORT_FULL_01":  jingyu/******** directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JINGYU2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing 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 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PRO_SELECT" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PROC_INSERT_BOOK2" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JINGYU"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Thu Jun 8 17:11:52 2017 elapsed 0 00:00:26

導入は完了するが、いくつかの警告があり、本実験に関連するのは「JINGYU 2」のみである.「PROC_INSERT_BOOK 2」編集警告処理は、以下の手順で詳細に説明します.

5.問題現象の再現と解決

問題の再現:テーブルの最大BOOKIDがシーケンスの現在値より大きいことをクエリーします.具体的な状況は以下の通りです.

SQL> select max(BOOKID) from book2;

MAX(BOOKID)
-----------
        505

SQL> 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 ignored
6/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 4
ORA-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;

これで実験全体が完成した.

《Cracking the Coding Interview》——第12章:テスト——テーマ3

Directory Not Empty、消せない幽霊