Re: ORA-01555: snapshot too old

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 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.
> >
> >
> >
> >
> >
  Received on Thu Jul 19 2001 - 07:34:47 CDT