`
coollifer
  • 浏览: 56061 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

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

分享到：
| DataTable.Select 字符串比较区分大小写
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle exp imp详解

    #### 二、EXP/IMP 的主要用途 1. **数据库转储**: 特别适用于小型数据库的完全或部分转储。 2. **表空间迁移**: 可以方便地迁移表空间。 3. **表的抽取**: 抽取特定的表进行备份或迁移。 4. **检测逻辑和物理冲突**:...

    exp imp详解

    - **CONSISTENT**: 设置导出过程的一致性级别。 - **CONSTRAINTS**: 导出或导入的约束条件。 #### 五、结论 通过对`exp`和`imp`命令的深入了解和应用，数据库管理员可以更加高效地管理和维护Oracle数据库。无论是...

    ORACLE自动备份方法

    exp %CITICTEST%/%CITICTEST%@COLM2 file=c:\backup\%DATE:~4,20%.dmp log=c:\backup\%DATE:~4,20%.log compress=Y direct=N rows=Y owner='CITICTEST' consistent=N constraints=Y grants=Y indexes=Y triggers=Y ...

    Oracle中用exp_imp命令参数详解

    exp system/manager@TEST rows=y indexes=y compress=n buffer=65536 feedback=100000 full=y file=d:\daochu.dmp log=d:\daochulog.txt owner=(ECC_BIZ,ECC_CUSTOMER) ``` - **导出指定用户的所有表**： ``` ...

    Oracle 导入和导出命令详解

    - **consistent=Y**: 设置一致性检查，默认为Y。 - **log=日志文件名**: 日志文件的名称。 - **statistics=ESTIMATE**: 设置统计信息收集方式，如estimate、actual等。 - **direct=N**: 是否采用直接路径模式，...

    EXP-IMP（Oracle数据导入导出）

    - **实际应用建议**：为了确保数据的一致性，在预见可能有其他应用程序同时访问数据的情况下，建议使用 CONSISTENT=Y 进行导出。 #### 2. EXP 实用程序的类型 - **COMPLETE 完全备份**：导出整个数据库的内容。 - ...

    Oracle数据备份(exp.exe)、恢复工具(imp.exe)使用方法.docx

    impsms/smssend@orazw fromuser=svcmtouser=sms rows=y commit=y buffer=65536 file=d:\exp_svcm_20060323.dmp log=d:\imp_svcm_20060323.log ``` 此示例中，`impsms/smssend@orazw`指定了用户名和密码以及连接字符...

    oracle＿exp_imp详解

    exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y ``` 这个命令会导出整个数据库的结构，并禁用数据行的导出，同时启用了数据压缩和直接路径。 - **IMP 示例**： `...

    Oracle exp imp命令详解

    exp SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT) INDEXES=Y ``` #### 二、Oracle imp 命令详解 **2.1 基本语法** ``` imp [username/password] [keyword=value] ``` **2.2 常用参数详解** - **USERID**：指定用于...

    在suse下添加oracle 11g自动备份

    exp username/password@sid file=/oracle_dmp/backup/$D1 log=/oracle_log/backup/$D2 compress=n buffer=10240 consistent=y direct=n constraints=y feedback=10000 grants=y record=y indexes=y triggers=y rows...

    oracle详解

    exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y 2. OWNER和TABLE，这两个选项用于定义EXP的对象。OWNER定义导出指定用户的对象；TABLE指定EXP的table名称，例如： exp...

    EXP、IMP命令详解

    exp system/manager full=y file=E:\SampleDB.dmp ``` - 导出特定用户的所有对象： ``` exp system/manager owner=(system, sys) file=E:\SampleDB.dmp ``` - 导出特定表： ``` exp system/manager file=E:...

    Oracle通过批处理实现自动备份

    expkdycps/kdycps987@ORCL full=y consistent=y file=D:\OraDataBak\KDYCPS_%date:~0,4%%date:~5,2%%date:~8,2%.dmp log=D:\OraDataBak\KDYCPS_%date:~0,4%%date:~5,2%%date:~8,2%.log ``` 该命令将数据库`ORCL`中的...

    Oracle 数据库导出(exp)导入(imp)说明

    exp dbuser/oracle file=oradb.dmp log=oradb.log full=y consistent=y direct=y ``` - 导出特定用户的所有对象： ``` exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 ...

    oracole备份数据库

    7. **consistent=y**: 表示进行一致性备份，即导出时会锁定表，确保数据的一致性和完整性。 8. **direct=n**: 表示不使用直接路径导出，即使用传统的 SQL*Loader 导入/导出方式。 9. **constraints=y**: 包括约束...

    consistent:Node.JS 的快速一致性哈希模块

    var consistent = require ( 'consistent' ) ; var ring = consistent ( { members : [ "member1" , "member2" , { key : "member3" , weight : 1.5 // optional, default weight is 1 } ] , hash : 'md5'...

    oracle11G_64位的imp.exe及exp.exe BIN包

    - `consistent`：导出时获取一致性快照，确保数据一致性。 三、Oracle Data Pump的优势 Oracle Data Pump比传统的`EXP`和`IMP`工具更快，因为它使用了并行数据传输和压缩技术。它还支持更多的导出和导入选项，允许...

Magicbox
Global site tag (gtag.js) - Google Analytics