exp,imp 小参数详解（之一）

consistent=y
object_consistent=y

||||||||||||||||||||||||||
CONSISTENT
Default: n

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.

Table 19-4 shows a sequence of events by two users: user1 exports partitions in a table and user2 updates data in that table.

Table 19-4 Sequence of Events During Updates by Two Users

TIme Sequence User1 User2
1 Begins export of TAB:P1 No activity
2 No activity Updates TAB:P2 Updates TAB:P1 Commits transaction
3 Ends export of TAB:P1 No activity
4 Exports TAB:P2 No activity

If the export uses CONSISTENT=y, none of the updates by user2 are written to the export file.

If the export uses CONSISTENT=n, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file, because the update transaction is committed before the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making it inconsistent.

If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower, because the rollback segment must be scanned for uncommitted transactions.

Keep in mind the following points about using CONSISTENT=y:

•CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.

•Export of certain metadata may require the use of the SYS schema within recursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.

•To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not. For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.

•A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.

Note:

Rollback segments will be deprecated in a future Oracle database release. Oracle recommends that you use automatic undo management instead.
See Also:

OBJECT_CONSISTENT

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

 

1.不支持 以sysdba权限用户在exp 条件上加上 consistent=y （•CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.）
2. 如果exp选择了consistent=y，那么对整个exp操作里，exp能够看到的数据都是在同一个时间点的，在exp操作过程中，它不会改变。（the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command.） ，造成的实际结果是数据库里导出的所有对象(object)都有同一个系统改变号(scn).
3.相反，如果在exp时，选择了consistent=n,一般情况下，每个表的导出都是在同一个事务中的，（If you use CONSISTENT=n, each table is usually exported in a single transaction），但也有特例，比如分区表，它会把每个分区作为独立的事务进行导出。
（If a table is partitioned, each partition is exported as a separate transaction.）比如嵌套表，它会把outer表和inner表的导出作为不同的事务导出。（ if a table contains nested tables, the outer table and each inner table are exported as separate transactions. ）
所以，在consistent=n的情况下，针对分区表，或者嵌套表的导出，exp出来数据的数据可能会不一致。为避免这种情况，建议在
exp的时候，选择 不对这些类型的表做update操作的时候。
(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.)
实际的情况原因是什么呢？
举了一个例子，四个时间段，
1.开始导出 tab:p1 分区，
2.某用户(A)修改tab表，这个操作同时修改了tab:p2 tab:p1两个分区，并提交了事务
3.tab:p1分区的导出结束了
4.开始导出tab:p2分区。

上面这个例子里，

如果选择了consistent=y，某用户（A)对 tab的修改不会被导出到exp 文件。
但是如果选择了,consisten=n,
A用户对tab:p1分区的修改不会被写到exp文件里去。
然而对tab:p2分区的修改就会被写入到exp文件里了。
这个时候，用户A对表tab的修改只有部分被录入到exp文件里，导致exp数据不一致了！
(TIme Sequence User1 User2
1 Begins export of TAB:P1 No activity
2 No activity Updates TAB:P2 Updates TAB:P1 Commits transaction
3 Ends export of TAB:P1 No activity
4 Exports TAB:P2 No activity

If the export uses CONSISTENT=y, none of the updates by user2 are written to the export file.

If the export uses CONSISTENT=n, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file, because the update transaction is committed before the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making it inconsistent.
)
4.consistent=y是很好很强大，但是对系统会有更大的资源损耗，且有可能出现快照过旧(ora-01550)错误。需要合理使用。
建议针对某些有关联的表选择consistent=y操作，其他表选择consistent=no。(For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.)这样可以尽量减少
read consistent exp的时间,（To avoid this error, you should minimize the time taken by a read-consistent export）
让回滚段尽可能大。（Also, make the rollback segment as large as possible）

(•To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not. For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.

•A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.)

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
OBJECT_CONSISTENTDefault: n

Specifies whether or not the Export utility uses the SET TRANSACTION READ ONLY statement to ensure that the data exported is consistent to a single point in time and does not change during the export. If OBJECT_CONSISTENT is set to y, each object is exported in its own read-only transaction, even if it is partitioned. In contrast, if you use the CONSISTENT parameter, then there is only one read-only transaction.

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
  流复制初始化操作经常需要在导出的时候加个参数,object_consistent=y;
 
  如果选择的是object_consistent=y，则可以保证exp导出的所有的内容在一个object(对象)范围内都属于一个系统改变号内(scn)。而如果选择的是 consistent=y，则整个库导出的内容都是在一个系统改变号内（scn)的。
  |||||||||||||||||||||||||||||
   总的结论：
1.不支持 以sysdba权限用户在exp 条件上加上 （consistent=y)
2. 如果exp选择了consistent=y，那么对整个exp操作里，exp能够看到的数据都是在同一个时间点的，在exp操作过程中，它不会改变。造成的实际结果是数据库里导出的所有对象(object)都有同一个系统改变号(scn).
3.相反，如果在exp时，选择了consistent=n,一般情况下，每个表的导出都是在同一个事务中的，但也有特例，比如分区表，它会把每个分区作为独立的事务进行导出。比如嵌套表，它会把outer表和inner表的导出作为不同的事务导出。所以，在consistent=n的情况下，针对分区表，或者嵌套表的导出，exp出来数据的数据可能会不一致。为避免这种情况，建议在exp的时候，选择 不对这些类型的表做update操作的时候。
4.consistent=y是很好很强大，但是对系统会有更大的资源损耗，且有可能出现快照过旧(ora-01550)错误。需要合理使用。
  建议针对某些有关联的表选择consistent=y操作，其他表选择consistent=no。这样可以尽量减少read consistent exp的时间,
  让回滚段尽可能大。
5.如果选择的是object_consistent，则可以保证exp导出的所有的内容在一个object(对象)范围内都属于一个系统改变号内(scn)。
      而如果选择的是 consistent=y，则整个库导出的内容都是在一个系统改变号内（scn)的。
6.如果选择object_consistent=y，或者consistent=y,都要考虑对系统的资源损耗。