对export的CONSISTENT参数的一次测试-CSDN博客

先来一段oracle document里对此参数的一段解释：

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.

If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.

这段话的大概意思是说如果CONSISTENT=y，如果在export过程中，应用在修改数据，而你又希望你导出的数据是一致的，所谓的一致指的是导出的数据是导出开始那一刻的，也就是SET TRANSACTION READ ONLY 的效果。

为了直观的显示这个效果，做以下测试：

1. CONSISTENT=n。

这种情况下，不保证导出数据的一致性，也就是说导出的数据不是一个时间点的。测试如下：

在一个session里执行如下命令，先后导出2个表test1和emp，其中test1有将近300万条记录，emp有14条记录

[oracle@ora-as4 ora9208]$ exp scott/tiger tables=(test1,emp) file=test.dmp CONSISTENT=n statistics=none

Export: Release 9.2.0.8.0 - Production on Mon May 5 15:42:09 2008

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHT16BIG5 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST1

当export开始导出test1时，在另外一个session里执行如下命令：

SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
----------
0

此时，session 1中的export仍然正在导出test1，稍后，导出完毕：

可见，导出emp时间点已经发生了变化，test1表和emp表里的数据不是同一时间点了。

2. CONSISTENT=y。

重复上述试验步骤，

session 1

[oracle@ora-as4 ora9208]$ exp scott/tiger tables=(test1,emp) file=test.dmp CONSISTENT=y statistics=none

Export: Release 9.2.0.8.0 - Production on Mon May 5 16:02:19 2008

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHT16BIG5 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST1

接着在session 2中：

SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

session 1的export执行完毕：