exp的consistent=Y的用途

最近客户提到关于exp出来的sequence小于表中数据的问题，研究了一下，解决了。
具体问题是这样的：表的主键是用sequence来生成的。在exp按user导出时，是先导出sequence然后再导出表，这就有可能发生这样的情况，
在sequence导出以后，在对应的表导出之前，有新的数据insert这张表，并且及时提交了，这使导出表的时候表中主键的值已经大于之前导出的seqence值。
如果把这个dmp文件导入到别的用户或是数据库中，对这个表进行插入，就会遇到主键冲突的错误。
这个问题的解决办法是在exp时加上consistent=y的参数，其作用是exp对所有要导出表的查询都是发生在同一时间的，也就是第一个表导出的那个时间，这样即使在sequence导出
之后有新的数据insert，exp也不会导出这些数据。
下面作实验验证。

 

--情况一：5个表，每个表有sequence，sequence是nocache的。在表导出过程中，不断插入数据。之后再imp，然后再插入数据，出现主键冲突错误。
SQL> create table t1 (a int);

表已创建。

SQL> create table t2 (a int);

表已创建。

SQL> create table t3 (a int);

表已创建。

SQL> create table t4 (a int);

表已创建。

SQL> create table t5 (a int);

表已创建。

SQL> create sequence seq_t1 increment by 1 start with 1 nocache;

序列已创建。

SQL> create sequence seq_T2 INCREMENT BY 1 START with 1 nocache;

序列已创建。

SQL> create sequence seq_t3 INCREMENT BY 1 START with 1 nocache;

序列已创建。

SQL> create sequence seq_t4 INCREMENT BY 1 START with 1 nocache;

序列已创建。

SQL> create sequence seq_t5 INCREMENT BY 1 START with 1 nocache;

序列已创建。

--循环100000次，每次向5个表插入数据。与此同时，对5个表进行导出。
SQL> begin
2 for i in 1..100000 loop
3 insert into t1 values (seq_t1.nextval);
4 insert into t2 values (seq_t2.nextval);
5 insert into t3 values (seq_t3.nextval);
6 insert into t4 values (seq_t4.nextval);
7 insert into t5 values (seq_t5.nextval);
8 commit;
9 end loop;
10 end;
11 /

PL/SQL 过程已成功完成。

D:oracleora92bin>exp test/test@test9 owner=test file=c:test.dmp

Export: Release 9.2.0.1.0 - Production on 星期三 10月 11 10:05:03 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 ZHS16CGB231280 字符集 (可能的字符集转换)

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 TEST 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 TEST 的对象类型定义
即将导出 TEST 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 TEST 的表通过常规路径 ...
. . 正在导出表 T1 15271 行被导出
. . 正在导出表 T2 15272 行被导出
. . 正在导出表 T3 15390 行被导出
. . 正在导出表 T4 15463 行被导出
. . 正在导出表 T5 15602 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计
在没有警告的情况下成功终止导出。

--可以看到导出的数据量是不一样的。而且是sequence先导出，表后导出。

--重建用户，导入数据。

D:oracleora92bin>imp test/test@test9 full=y file=c:test.dmp

Import: Release 9.2.0.1.0 - Production on 星期三 10月 11 10:21:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
导入服务器使用ZHS16CGB231280字符集 (可能的字符集转换)
. 正在将TEST的对象导入到 TEST
. . 正在导入表 "T1" 15271行被导入
. . 正在导入表 "T2" 15272行被导入
. . 正在导入表 "T3" 15390行被导入
. . 正在导入表 "T4" 15463行被导入
. . 正在导入表 "T5" 15602行被导入
成功终止导入，但出现警告。

SQL> conn test/test
已连接。
SQL> alter table t1 add primary key (a);

表已更改。

SQL> alter table t2 add primary key (a);

表已更改。

SQL> alter table t3 add primary key (a);

表已更改。

SQL> alter table t4 add primary key (a);

表已更改。

SQL> alter table t5 add primary key (a);

表已更改。

--可以看到，T2到T5这4个表主键的最大值都不小于对应sequence的LAST_NUMBER，这样再插入数据就会导致主键冲突。

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME LAST_NUMBER
------------------------------------------------------------ -----------
SEQ_T1 15273
SEQ_T2 15272
SEQ_T3 15272
SEQ_T4 15272
SEQ_T5 15272

SQL> insert into t1 values (seq_t1.nextval);

已创建 1 行。

SQL> insert into t2 values (seq_t2.nextval);
insert into t2 values (seq_t2.nextval)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C001579)

SQL> insert into t3 values (seq_t3.nextval);
insert into t3 values (seq_t3.nextval)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C001580)

SQL> insert into t4 values (seq_t4.nextval);
insert into t4 values (seq_t4.nextval)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C001581)

SQL>
SQL> insert into t5 values (seq_t5.nextval);
insert into t5 values (seq_t5.nextval)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C001582)

SQL> commit;

提交完成。

 

--第2种情况，将sequence cache 1000。这样exp时，exp不会导出sequence的当前值，而是导出sequence cache的上限值，这会使报主键冲突的几率降低，但不能彻底解决这个问题。

SQL> conn test/test
已连接。
SQL> create table t1 (a int);

表已创建。

SQL> create table t2 (a int);

表已创建。

SQL> create table t3 (a int);

表已创建。

SQL> create table t4 (a int);

表已创建。

SQL> create table t5 (a int);

表已创建。

SQL> create sequence seq_t1 increment by 1 start with 1 cache 1000;

序列已创建。

SQL> create sequence seq_t2 increment by 1 start with 1 cache 1000;

序列已创建。

SQL> create sequence seq_t3 increment by 1 start with 1 cache 1000;

序列已创建。

SQL> create sequence seq_t4 increment by 1 start with 1 cache 1000;

序列已创建。

SQL> create sequence seq_t5 increment by 1 start with 1 cache 1000;

序列已创建。

SQL> begin
2 for i in 1..100000 loop
3 insert into t1 values (seq_t1.nextval);
4 insert into t2 values (seq_t2.nextval);
5 insert into t3 values (seq_t3.nextval);
6 insert into t4 values (seq_t4.nextval);
7 insert into t5 values (seq_t5.nextval);
8 commit;
9 end loop;
10 end;
11 /

PL/SQL 过程已成功完成。

--在上面插入数据的同时，导出5个表的数据。

D:oracleora92bin>exp test/test@test9 owner=test file=c:test.dmp

Export: Release 9.2.0.1.0 - Production on 星期三 10月 11 10:46:58 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 ZHS16CGB231280 字符集 (可能的字符集转换)

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 TEST 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 TEST 的对象类型定义
即将导出 TEST 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 TEST 的表通过常规路径 ...
. . 正在导出表 T1 38858 行被导出
. . 正在导出表 T2 38859 行被导出
. . 正在导出表 T3 38906 行被导出
. . 正在导出表 T4 38937 行被导出
. . 正在导出表 T5 39089 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计
在没有警告的情况下成功终止导出。

 

SQL> conn / as sysdba
已连接。
SQL> drop user test cascade;

用户已删除。

SQL> grant dba to test identified by test;

授权成功。

--导入数据，略。

--由于sequence都设置了cache 1000，所以导出的sequence都是取的当时的cache的上限值(最大值)。

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME LAST_NUMBER
------------------------------------------------------------ -----------
SEQ_T1 39001
SEQ_T2 39001
SEQ_T3 39001
SEQ_T4 39001
SEQ_T5 39001

SQL> alter table t1 add primary key (a);

表已更改。

SQL> alter table t2 add primary key (a);

表已更改。

SQL> alter table t3 add primary key (a);

表已更改。

SQL> alter table t4 add primary key (a);

表已更改。

SQL> alter table t5 add primary key (a);

表已更改。

SQL> insert into t1 values (seq_t1.nextval);

已创建 1 行。

SQL> insert into t2 values (seq_t2.nextval);

已创建 1 行。

SQL> insert into t3 values (seq_t3.nextval);

已创建 1 行。

SQL> insert into t4 values (seq_t4.nextval);

已创建 1 行。

SQL> insert into t5 values (seq_t5.nextval);
insert into t5 values (seq_t5.nextval)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C001587)

--最后一个表导出时，数据已经到了39089条，大于最开始导出的sequence的值，这就导致了后来的主键冲突的发生。

SQL> commit;

提交完成。

 

--第3种情况，在exp时加上参数consistent=y，这样5个表导出的数据量是一样的，实际上exp在内部记录了第一个表导出时数据库的scn，然后以后每个表导出都是按照这个scn进行的查询。这就保证了一致性。这样exp过程中insert的数据自然就不会被导出。也就避免了后来的主键冲突。

 

SQL> create table t1 (a int);

表已创建。

SQL> create table t2 (a int);

表已创建。

SQL> create table t3 (a int);

表已创建。

SQL> create table t4 (a int);

表已创建。

SQL> create table t5 (a int);

表已创建。

SQL> create sequence seq_t1 increment by 1 start with 1 nocache;

序列已创建。

SQL> create sequence seq_t2 increment by 1 start with 1 nocache;

序列已创建。

SQL> create sequence seq_t3 increment by 1 start with 1 nocache;

序列已创建。

SQL> create sequence seq_t4 increment by 1 start with 1 nocache;

序列已创建。

SQL> create sequence seq_t5 increment by 1 start with 1 nocache;

序列已创建。

SQL> begin
2 for i in 1..50000 loop
3 insert into t1 values (seq_t1.nextval);
4 insert into t2 values (seq_t2.nextval);
5 insert into t3 values (seq_t3.nextval);
6 insert into t4 values (seq_t4.nextval);
7 insert into t5 values (seq_t5.nextval);
8 commit;
9 end loop;
10 end;
11 /

PL/SQL 过程已成功完成。

D:oracleora92bin>exp test/test@test9 owner=test file=c:test.dmp consistent=y

Export: Release 9.2.0.1.0 - Production on 星期三 10月 11 14:43:21 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 ZHS16CGB231280 字符集 (可能的字符集转换)

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 TEST 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 TEST 的对象类型定义
即将导出 TEST 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 TEST 的表通过常规路径 ...
. . 正在导出表 T1 11305 行被导出
. . 正在导出表 T2 11305 行被导出
. . 正在导出表 T3 11305 行被导出
. . 正在导出表 T4 11305 行被导出
. . 正在导出表 T5 11305 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计
在没有警告的情况下成功终止导出。

SQL> drop user test cascade;

用户已删除。

SQL> grant dba to test identified by test;

授权成功。

D:oracleora92bin>imp test/test@test9 full=y file=c:test.dmp

Import: Release 9.2.0.1.0 - Production on 星期三 10月 11 14:46:50 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
导入服务器使用ZHS16CGB231280字符集 (可能的字符集转换)
. 正在将TEST的对象导入到 TEST
. . 正在导入表 "T1" 11305行被导入
. . 正在导入表 "T2" 11305行被导入
. . 正在导入表 "T3" 11305行被导入
. . 正在导入表 "T4" 11305行被导入
. . 正在导入表 "T5" 11305行被导入
成功终止导入，但出现警告。

SQL> conn test/test
已连接。

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME LAST_NUMBER
------------------------------------------------------------ -----------
SEQ_T1 11307
SEQ_T2 11306
SEQ_T3 11306
SEQ_T4 11306
SEQ_T5 11306

SQL> alter table t1 add primary key (a);

表已更改。

SQL> alter table t2 add primary key (a);

表已更改。

SQL> alter table t3 add primary key (a);

表已更改。

SQL> alter table t4 add primary key (a);

表已更改。

SQL> alter table t5 add primary key (a);

表已更改。

SQL> insert into t1 values (seq_t1.nextval);

已创建 1 行。

SQL> insert into t2 values (seq_t2.nextval);

已创建 1 行。

SQL> insert into t3 values (seq_t3.nextval);

已创建 1 行。

SQL> insert into t4 values (seq_t4.nextval);

已创建 1 行。

SQL> insert into t5 values (seq_t5.nextval);

已创建 1 行。

SQL> commit;

提交完成。

 

转载出处：http://haochunpeng.itpub.net/post/385/220381