簡單描述邏輯匯出SEQUENCE後，匯入到目標環境利用SEQUENCE插入時出現唯一衝突的問題。

以邏輯方式匯出，在匯出過程中，被匯出的物件會同時被其他會話訪問，因此即使匯出後馬上匯入，匯入的物件也會和資料來源有所差別。

其中一個比較常見的問題就是，序列變化的問題。由於匯出的過程中序列仍然可能被訪問，但是匯入後利用這個序列生成唯一鍵值，可能出現ORA-00001錯誤。

SQL> conn u1/u1
Connected.
SQL> create table t_big as select * from all_objects;

Table created.

SQL> insert into t_big select * from t_big;

40801 rows created.

SQL> insert into t_big select * from t_big;

81602 rows created.

SQL> insert into t_big select * from t_big;

163204 rows created.

SQL> insert into t_big select * from t_big;

326408 rows created.

SQL> insert into t_big select * from t_big;

652816 rows created.

SQL> insert into t_big select * from t_big;

1305632 rows created.

SQL> insert into t_big select * from t_big;

2611264 rows created.

SQL> insert into t_big select * from t_big;

5222528 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t_big;

COUNT(*)
----------
10445056

SQL> create table t (id number primary key, name varchar2(30));

Table created.

SQL> create sequence s1 nocache;

Sequence created.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

構造一個百萬的大表，然後對資料庫執行匯出：

[oracle@yans1 ~]$ expdp u1/u1 dumpfile=u1.dp logfile=u1.log directory=d_output

Export: Release 10.2.0.3.0 - 64bit Production on 星期六, 01 5月, 2010 16:15:44

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** dumpfile=u1.dp logfile=u1.log directory=d_output
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 GB
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/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/COMMENT

之所以需要構造一張大表，就是希望匯出不要很快結束，在匯出SEQUENCE後，資料泵匯出表資料的時候，回到開始的會話，利用序列繼續插入資料：

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

插入3條記錄並提交。

這時匯出操作完成。

. . exported "U1"."T_BIG" 988.0 MB 10445056 rows
. . exported "U1"."T" 5.257 KB 6 rows
Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/u1.dp
Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:16:58

可以看到Oracle對於T表匯出了6條記錄。而SEQUENCE佇列的定義在此之前就匯出了。如果利用IMPDP匯入，就會導致ORA-00001錯誤。

SQL> drop table t purge;

Table dropped.

SQL> drop sequence s1;

Sequence dropped.

下面匯入T表和S1序列：

[oracle@yans1 ~]$ impdp u1/u1 dumpfile=u1.dp logfile=u1.log directory=d_output exclude=schema_export/table:\"=\'T_BIG\'\"

Import: Release 10.2.0.3.0 - 64bit Production on 星期六, 01 5月, 2010 19:32:54

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "U1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U1"."SYS_IMPORT_FULL_01": u1/******** dumpfile=u1.dp logfile=u1.log directory=d_output exclude=schema_export/table:"='T_BIG'"
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 "U1"."T" 5.257 KB 6 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "U1"."SYS_IMPORT_FULL_01" successfully completed at 19:32:57

利用序列執行插入：

SQL> insert into t values (s1.nextval, 'a');
insert into t values (s1.nextval, 'a')
*
ERROR at line 1:
ORA-00001: unique constraint (U1.SYS_C0066655) violated

SQL> select s1.currval from dual;

CURRVAL
----------
4

正如前面分析的原因，這裡出現ORA-00001錯誤。

其實解決這個問題並不複雜，如果可以將資料庫至於RESTRICT SESSION模式下，執行匯出，就可以避免這個錯誤的，不過這種方式對系統的影響比較大。

還有一種簡單易行的方法，就是利用序列的CACHE。將SEQUENCE的CACHE設定為一個較大的值，確保匯出序列和匯出表資料之前的時間內，序列CACHE的值不會被用完，這就保證了匯入後不會出現序列值的NEXTVAL小於表中已有資料的情況了。

SQL> alter sequence s1 cache 100;

Sequence altered.

SQL> select s1.nextval from dual;

NEXTVAL
----------
5

SQL> select s1.nextval from dual;

NEXTVAL
----------
6

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

執行同樣的匯出操作：

[oracle@yans1 ~]$ rm u1.dp
[oracle@yans1 ~]$ expdp u1/u1 dumpfile=u1.dp logfile=u1.log directory=d_output

Export: Release 10.2.0.3.0 - 64bit Production on 星期六, 01 5月, 2010 19:41:12

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** dumpfile=u1.dp logfile=u1.log directory=d_output
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 GB
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/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/COMMENT

同樣在匯出的時候在另外的會話執行插入操作：

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> commit;

等待匯出操作結束：

. . exported "U1"."T_BIG" 988.0 MB 10445056 rows
. . exported "U1"."T" 5.304 KB 11 rows
Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/u1.dp
Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:41:25

下面刪掉T表和S1序列：

SQL> drop table t purge;

Table dropped.

SQL> drop sequence s1;

Sequence dropped.

利用匯出的檔案恢復T表和S1序列：

[oracle@yans1 ~]$ impdp u1/u1 dumpfile=u1.dp logfile=u1.log directory=d_output exclude=schema_export/table:\"=\'T_BIG\'\"

Import: Release 10.2.0.3.0 - 64bit Production on 星期六, 01 5月, 2010 19:42:41

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "U1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U1"."SYS_IMPORT_FULL_01": u1/******** dumpfile=u1.dp logfile=u1.log directory=d_output exclude=schema_export/table:"='T_BIG'"
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 "U1"."T" 5.304 KB 11 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "U1"."SYS_IMPORT_FULL_01" successfully completed at 19:42:42

下面再次利用序列執行插入語句：

SQL> insert into t values (s1.nextval, 'a');

1 row created.

SQL> select s1.currval from dual;

CURRVAL
----------
105

可以看到，利用CACHE的功能，避免了錯誤的發生。

解決邏輯匯出後SEQUENCE的值發生變化的問題

相關文章