EXP中加入CONSISTENT=Y的疑问

PURPOSE OF CONSISTENT=Y EXPORT
------------------------------

Export extracts data by issuing a series of SELECT statements and the snapshot time of each table's data corresponds to when the SELECT statement was issued for that table. If there is no database activity, then this is not an issue.
However, it is possible to manipulate the table after the export has started in which case the snapshot of the data may be a factor. Export does not get an exclusive lock on any table.

There is an option called CONSISTENT=Y. When this is enabled, EXPORT first issues a SET TRANSACTION READ ONLY command before doing the export. There is a risk of running out of rollback segment space and getting an "ORA-1555 snapshot too old (rollback segment too small)" error when doing a long running export.


CONSISTENT EXPORT PARAMETER
---------------------------

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 export 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 specify CONSISTENT=N (the default), each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each innter 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.

The following chart shows a sequence of events by two users: USER1 exports partitions in a table and USER2 updates data in that table.

Time Sequence USER1 USER2
========== ===== =====
1 Begins export of TAB:P1

2 Updates TAB:P2
Updates TAB:P1
Commit transaction
3 Ends export of TAB:P1
4 Exports TAB:P2

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, USER2's 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 should be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transacitons.

Keep in mind the following points using CONSISTENT=Y:

-- 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.

-- To reduce the chances of encountering a "ORA-1555 snapshot too old" error, export the minimum number of objects that must be guaranteed consistent.

The "ORA-1555 snapshot too old" error occurs when rollback space has been used up, and space taken up by committed transactions is reused for new transactions,
or if OPTIMAL size is set unused extends could be deallocated.
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 or deallocated and the inforamtion is needed for a read-consistent view of the database, an "ORA-1555 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: You cannot specify CONSISTENT=Y with an incremental export.

SET TRANSACTION READ ONLY
-------------------------

You can establish transaction level read consistency by issuing a SET TRANSACTION statement with the READ ONLY option. After a transaction has been established as read-only, all subsequent queries in that transaction only see changes committed before the transaction began.
Read-only transactions are very useful for reports that run multiple queries against one or more tables while other users update these same tables.

Only the following statements are permitted in a read-only transaction:

-- SELECT (except statements with FOR UPDATE clause)
-- LOCK TABLE
-- SET ROLE
-- ALTER SESSION
-- ALTER SYSTEM

INSERT, UPDATE, and DELETE statements and SELECT statements with the FOR UPDATE clause are not permitted. Any Data Definition Language statement
implicitly ends the read-only transaction.

The read consistency that read-only transactions provide is implemented in the same way as statement-level read consistency. Every statement by default uses a consistent view of the data sa of the time the statement is issued. Read-only transactions present a consistent view of the data as of the time that the SET TRANSACTION READ ONLY statement is issued. Read-only transactions provide read consistency for all nodes accessed by distributed queries and local queries.

You cannot toggle between transaction level read consistency and statement level read consistency in the same transaction. A SET TRANSACTION statement can only be issued as the first statement of a transaction.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7882490/viewspace-179677/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7882490/viewspace-179677/