Just 2 days ago I replied on a ORA-01555 too.
An export with consistent=y could cause ORA-01555. You can use consistent=n
but apparently that's not what you want. In a consistent export the
complete export is made with the data as seen at the beginning, so not per
statement.
Below the text of my previous mail to explain ORA-01555. Hopefully you
understand from that what to do.

ORA-1555 is an often misunderstood error. Let me try to explain as short as
possible.

In Oracle the "statement level consistent read" principle exists. That means
that a statement will see the data as it was at the moment the statement
started, regardless how long it runs. This is a very big pro of Oracle.
So at timestamp T1 you start a SELECT ... FROM table1 .....
While Oracle execute this select other sessions do insert/updates/deletes on
table1 from T2 till T3. Oracle stores before/after images of the changes in
the blocks affected in the rollback segments. This is required to rollback
these changes when you do rollback. After a rollback or a commit this
rollback data is no longer required. Other sessions are allowed to re-use
this space in the rollback segments.
Now your SELECT ... FROM table1 ..... reads a block from table1 at T4 and it
sees that it has changed since timestamp T1. It also knows where in the
rollback segments the rollback data is to recreate the previous version of
the block (and maybe another earlier version etc.. ) as it was at T1.
As long as the sessions that made these changes have not committed, the
rollback data is certainly there.
When the updating sessions have committed there transactions then there is a
chance the rollback data is already overwritten by other sessions.
When your SELECT ... FROM table1 ... needs to reconstruct a block (in Oracle
terms: needs a consistent version of the block) and the undo data (an other
word for rollback data) is no longer there you get ORA-1555 Snapshot too
old.

It's obvious that this can occur when:
- your query runs long
- concurrently updates of the tables used in the query take place.

What can you do about it? You can only reduce the risk that it happens, not
eliminate it, by taking following measures:
- as always: optimize your query; maybe the execution plan is bad and it
does not need to run so long; when using the Cost based optimizer (CBO) make
sure the statistics are up to date.
- run the query when there is no or minimal update activity
- have sufficient rollback segments online and size them properly (like
minimum of 20 extents); so it takes longer before space is reused. You can
find good documents on Technet or Metalink that explain proper rollback
segment sizing.


----- Original Message -----
From: Kamran Malik <kamran.[Email Address Removed] LazyDBA.com Discussion <[Email Address Removed] Friday, May 31, 2002 5:06 PM
Subject: ORA-01555


| Hi All,
|
| Whilst carrying out an Export I received the following error:
|
| ORA-01555 - Snapshot too old: rollback segment number 1 with name RBS1 too
| small. Has anyone had this error, if so I'd appreciate it you could let
me
| your input. Thanks in advance.
|
| Kind regards,
|
|
| Kamran
|
|
|
|
|
| --------
| Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
| To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
| Tell yer mates about http://www.farAwayJobs.com
| By using this list you agree to these
terms:http://www.lazydba.com/legal.html
|
|

Oracle LazyDBA home page