Re: ORA-01555: snapshot too old

In article <3b566412_at_usenet.per.paradox.net.au>, "Howard says...
>
>Oh go on, I'll add my 2 cents'-worth...
>
>Dave's right in saying that increasing minextents will help cure 1555's.
>But that's frankly a daft way to increase the size of rollback segments
>(well, OK, not daft, but not entirely wholesome, either).
>
>If you're using dictionary managed tablespace, then no segment should
>(ideally) have more than around half a dozen extents or so, and that goes
>for rollback segments, too.
>

your kidding. What's the basis in reality for that comment about not have more the 6 extents?

Most all rbs's should have more then 6 extents.

Having a couple of hundred extents is no big deal. Where is the scientific proof, case study, example even that shows otherwise?

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:730289259844

>Why not simply recreate your rollback segments with the same number of
>extents, but make the extent sizes bigger? In other words, play around with
>INITIAL and NEXT, not MINEXTENTS.
>
>Regards
>HJR
>
>"Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote in message
>news:8Qa57.85596$Do6.3950598_at_nnrp4.clara.net...
>>
>> "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.
>>
>>
>>
>>
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp