Re: Snapshot old

Comments embedded.
On Jun 1, 3:17 pm, newbie <rjngh2..._at_gmail.com> wrote:
> I had a question about Snapshot too old. while doing an export.
>
> when Oracle does an export of a database/schema/tables
> it sets a mark in the database and then all further transactions are
> sent to redo logs.
>

No. Export is not a backup. ALTER TABLESPACE ... BEGIN BACKUP; performs that way.

> So If the export takes a long time (if we are exporting a large schema
> or full database)
> and if the redo log size is small and if there are a number of
> transactions being done by the Instance in this case after the redo
> logs get full the export aborts with a message
> Snapshot too old.
>
> Is this correct.....?

No. Oracle, by default, performs a 'table-consistent' export of the structure and data, meaning that export operates like any other query against a table where data is being modified and the modifications have not yet been committed or the commit was issued after the query started but before the query finished returning data. As an example exp is processing TAB.P1 and a user is updating both TAB.P1 and TAB.P2, committing the changes after the export of TAB.P! started but before that portion of the export process completed. No changes to TAB.P! will be found in the dmp file; all changes to TAB.P2 committed before that table's export begins will be found in the dmp file. Using CONSISTENT=Y forces export to take a cross-table consistent view of the data and attempt to preserve that consistency in the dmp file. CONSISTENT=Y is a good way to generate an ORA-01555 as the rollback segments may be too small to contain all of the necessary undo to produce such consistency.

>
> Solution to the above problem is to increase the redo log size.

Why?

>
> Does Undo tablespace have to be increased ?

It may need to be, but not for the 'reasons' you claim.

David Fitzjarrell Received on Fri Jun 01 2007 - 15:44:02 CDT