Re: ORA-01555: snapshot too old

Yu wrote:

> We have a cron job every night to exp some schemas(oracle 8.05 on Sun). We
> got the following error during last night's exp for the first time:
 (snip)
> According to doc, the error is due to smaller/fewer rollback segments. But
> why R02 HWMSIZE is only 164M ( 163729408 ) when it could use 1024M? Or
 were
> there too many other transactions going on (therefore overwrite data in
> rollback segments) during the exp that cause this error?

I'm figuring that you did, indeed, have active transactions going on in the database during the export & one or more of these transactions issued a COMMIT before the export ended. When a transaction COMMITs, Oracle makes the undo records available to be overwritten by other transactions even though it also considers these undo records to be "inactive, in-use" if some other operation needs them for read-consistency. The text of the error message -- "rollback segment too small" -- is misleading. Once a transaction commits, the space it was using in the rollback segment is now up for grabs -- size is not the issue. n other words it's a scheduling problem. You have two options I think:

1. Find a different time for the export or figure out some way to ensure that it has exclusive use of the database (ould be hard to do.)
2. Run the export with the parameter CONSISTENT=Y. This parameter sets a consistency point for the *entire* export operation and it will then have its own undo records to maintain consistency as of the begining of the export operation regardless of what else is going on in the database. I haven't tested this proposition myself, but I think this would solve your rpoblem. Just make sure that your rollback segments can grow becuase the export itself will end up generating a lot of undo if there is a lot of DML going on while it is running, since the entire export operation is now one big transaction.