Specifies whether 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, then each table is usually exported in a single transaction. However, if a table contains nested tables, then the outer table and each inner table are exported as separate transactions. If a table is partitioned, then each partition is exported as a separate transaction.
Therefore, if nested tables and partitioned tables are being updated by other applications, then 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 22-2 shows a sequence of events by two users: user1 exports partitions in a table and user2 updates data in that table.
Table 22-2 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, then none of the updates by user2 are written to the export file.
If the export uses CONSISTENT=n, then 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, then 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, then 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.
Rollback segments will be deprecated in a future Oracle database release. Oracle recommends that you use automatic undo management instead.