Re: ORA-01555: snapshot too old

Quite simply, that graph is based on Oracle 6 data, when transactions couldn't share *extents* -so naturally, you'd need lots of extents to stop the segment wrapping back on top of itself. That's not been true since Oracle 7.1, though, so 20 is unnecessary.

Regards
HJR "Stephen Bell" <stephen.bell_at_cgi.ca> wrote in message news:3B56D3E7.748C3C00_at_cgi.ca...
> Hi Howard,
>
> I agree with your comments....but in the Oracle Education material for the
 DBA
> course they talk about setting minextents to 20 (header contention i
> assume)..they go to great lengths (even a nice little graph if I recall
> correctly) to explain why...am I totally off base here? Or perhaps this is
 an
> "it depends on what you're doing" scenario...
>
> Any thoughts appreciated..
>
> Steve
>
> "Howard J. Rogers" wrote:
>
> > 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.
> >
> > 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
 rabs --
 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.
> > >
> > >
> > >
> > >
> > >
>
Received on Fri Jul 20 2001 - 22:39:10 CDT