Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-01555 Mystery (Help)

RE: ORA-01555 Mystery (Help)

From: Nick Wagner <Nick.Wagner_at_quest.com>
Date: Fri, 25 Jan 2002 15:32:22 -0800
Message-ID: <F001.003FBE8A.20020125153028@fatcity.com>

would "Set transaction read only" help here?

-----Original Message-----
From: Baker, Barbara [mailto:bbaker_at_denvernewspaperagency.com] Sent: Friday, January 25, 2002 2:35 PM
To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help)

I think you might have to throw a full hint in there to insure you're really touching all the blocks (select /*+ FULL*/ count(*) from table_name).

But Jared's correct.  If you're gonna go to that much trouble, might as well compute statistics.

> ----------
> From:         Jared.Still_at_radisys.com[SMTP:Jared.Still_at_radisys.com]
> Reply To:     ORACLE-L_at_fatcity.com
> Sent:         Friday, January 25, 2002 3:05 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:      RE: ORA-01555 Mystery (Help)
>
> I don't have a definitive answer for that.
>
> My guess would be that 'compute' would be required
> so that all blocks are touched.
>
> Another  way of dealing with delayed block cleanouts
> is to do a 'select * from table;'.    As long as you are
> going to touch every block anyway, you might as
> well compute the stats.
>
> But now I'm speculating.  :)
>
> Jared
>
>
>
>
>
> Walter K <alden14004_at_YAHOO.COM>
> Sent by: root_at_fatcity.com
> 01/25/02 11:20 AM
> Please respond to ORACLE-L
>

>  
>         To:     Multiple recipients of list ORACLE-L

> <ORACLE-L_at_fatcity.com>
>         cc: 
>         Subject:        RE: ORA-01555 Mystery (Help)

>
>
> Jared, would you elaborate more on this? Does this
> need to be a 'compute' or can it be an 'estimate' on
> the analyze?
>
> I read the info on Steve's site as suggested by Barb
> and it sounds like block cleanout may be the issue but
> I'm still trying to digest the concept/issue as it
> relates to my circumstance.
>
> For the others that have contributed to the thread,
> yes, the table is definitely locked in exclusive mode
> (via a different session) before the SELECT is
> performed and the lock is not released until the
> following day. I too was suspicious that the lock was
> accidentally being released.
>
> -w
>
>
>
>
> --- Jared.Still_at_radisys.com wrote:
> > Delayed block cleanouts can still cause the
> > ORA-1555, even
> > after locking the table in exlusive mode.
> >
> > That's the purpose of the analyze, to force the
> > block cleanouts.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > Paul Baumgartel <treegarden_at_YAHOO.COM>
> > Sent by: root_at_fatcity.com
> > 01/25/02 09:30 AM
> > Please respond to ORACLE-L
> >
> >
> >         To:     Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> >         cc:
> >         Subject:        RE: ORA-01555 Mystery (Help)
> >
> >
> > Sure, but the original post concerns a *query*, not
> > a transaction, and
> > before running the query, the user locked the
> > queried table in
> > exclusive mode, to ensure that no other session
> > could write to the
> > queried table.   How do we account for the query's
> > need to read from
> > rollback?
> >
> >
> > --- "Baker, Barbara"
> > <bbaker_at_denvernewspaperagency.com> wrote:
> > >
> > > I have a batch job that does this consistently.
> > It's the only job in
> > > the
> > > database; it sets the transaction to a hugh
> > rollback segment.  And it
> > > eats
> > > its own tail.
> > >
> > > Depending on how the job is written, it may need a
> > read consistent
> > > view
> > > itself (as opposed to some other query in the
> > database needing that
> > > read
> > > consistent view.)    In that case, it may well go
> > try to read its own
> > > rollback segment, only to find that it's been
> > overwritten.  (Oddly
> > > enough,
> > > even when there's plenty of space to extend the
> > rollback, Oracle will
> > > decide
> > > to overwrite the original rollback segments rather
> > than extend if it
> > > thinks
> > > it doesn't need those segments any more.)
> > >
> > > I'd strongly suggest you get the stuff from Steve
> > Adams' ixora site
> > > that
> > > places an uncommitted transaction in your rollback
> > segments for the
> > > length
> > > of the run.    This will guarantee that the
> > rollback segments don't
> > > get
> > > overwritten.
> > > Good luck!
> > >
> > > Barb
> > >
> > > > ----------
> > > > From:                Walter
> > K[SMTP:alden14004_at_yahoo.com]
> > > > Reply To:            ORACLE-L_at_fatcity.com
> > > > Sent:                Friday, January 25, 2002
> > 9:15 AM
> > > > To:                  Multiple recipients of list
> > ORACLE-L
> > > > Subject:             ORA-01555 Mystery (Help)
> > > >
> > > > Hi,
> > > >
> > > > A user in our data warehousing group is running
> > into
> > > > the old ORA-01555 (snapshot too old) error every
> > time
> > > > she runs a massive (20 million rows) select
> > against
> > > > one table via a view. I confirmed that the view
> > only
> > > > translates to the one table.
> > > >
> > > > The user swears that no one would be making any
> > > > updates/deletes to the table she is selecting
> > from. I
> > > > suggested she lock the table in exclusive mode,
> > prior
> > > > to running her massive select to guarantee no
> > one else
> > > > could change the data in the table and cause the
> > > > triggering of the 1555 error. Locking the table
> > was a
> > > > viable option because it's a staging table in
> > the
> > > > warehouse itself. She locked the table in
> > exclusive
> > > > mode last night and it locked; fired off her
> > query,
> > > > and it failed 5 hours later with the 1555 error
> > again.
> > > >
> > > > I'm stumped on this. I just don't see how this
> > is
> > > > possible. Any suggestions?
> > > >
> > > > Thanks!!!
> > > > -w
> > > >
> > > >
> > __________________________________________________
> > .
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> >   INET: Jared.Still_at_radisys.com
> >
> > Fat City Network Services    -- (858) 538-5051  FAX:
> > (858) 538-5051
> > San Diego, California        -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Walter K
>   INET: alden14004_at_yahoo.com

>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: Jared.Still_at_radisys.com

>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.com


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 25 2002 - 17:32:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US