Re: ORA-01555: snapshot too old

"Vincent Ventrone" <vav_at_brandeis.edu> wrote in message news:9ivkms$qni$1_at_new-news.cc.brandeis.edu...
>>"Guang Mei" <gmei_at_proteome.com> wrote in message news:kcI47.30B6.4620_at_news.shore.net...
>> 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:
>>
>> EXP-00008: ORACLE error 1555 encountered
>>
> 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.

Vincent's explanation of why snapshot too old has occurred is correct, but his second recommendation is wrong.

Using the consistent=y option will indeed make the whole of the export internally consistent, but it won't avoid the snapshot too old message - in fact it will make it more likely. The export is basically only running selects, it isn't doing any DML and so doesn't generate any undo records to maintain consistency - it is relying on the undo records created by other DML statements not being overwritten, just as it does when the consistent=y option is not used. Since consistent=y means the database has to maintain a consistent view of the entire database from the time the export starts
(rather than just a consistent view of each table, from the time each table is exported), it is much more likely that a required undo record will be overwritten in that time. Using consistent=y is a good idea, particularly if you hope to be able to import more than one table from the export and have them consistent, but it won't help your snapshot too old problem.

As you know, increasing max-extents for your rollback segments won't help, because they're not extending anyway, for the reasons Vincente pointed out. However, recreating your rollback segments with *min-extents* significantly bigger *will* help because you are pre-allocating them, so there is more space in the rollback segment to use for undo records before they are overwritten. If there is sufficient space in the rollback segment to accommodate all the undo generated by any DML running during the export, no undo records will be overwritten, so no snapshot too old will occur.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.