简单描述逻辑导出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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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的功能，避免了错误的发生。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-661758/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-661758/
281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
