Re: ORA-01555
From: joel garry <joel-garry_at_home.com>
Date: Thu, 14 Oct 2010 08:52:10 -0700 (PDT)
Message-ID: <80a727d6-9da3-4a03-97c3-d1529f59399a_at_k30g2000vbn.googlegroups.com>
On Oct 13, 2:18 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Wed, 13 Oct 2010 21:14:20 +0000, Mladen Gogala wrote:
> > I received ORA-01555. That in itself wouldn't be much to brag about, but
> > the circumstances are curious. Being aware that this is a large table I
> > imposed a shared lock on the table to prevent any concurrent updates.
> > Despite the lock and despite manually executing a global checkpoint
> > (yes, I need the keyword "global", there are 2 instances accessing the
> > DB). The lock was granted, there were no commits in my transaction nor
> > was the table updated. I checked the audit records, nothing there. How
> > is it possible? Usually, I was receiving ORA-01555 when long running
> > query was accessing table that has been concurrently modified. This
> > time, there were no concurrent updates.
>
> BTW, I am re-running now with the entire tablespace being turned to read
> only. If I get ORA-01555, I will start drinking.
>
> --http://mgogala.byethost5.com
Date: Thu, 14 Oct 2010 08:52:10 -0700 (PDT)
Message-ID: <80a727d6-9da3-4a03-97c3-d1529f59399a_at_k30g2000vbn.googlegroups.com>
On Oct 13, 2:18 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Wed, 13 Oct 2010 21:14:20 +0000, Mladen Gogala wrote:
> > I received ORA-01555. That in itself wouldn't be much to brag about, but
> > the circumstances are curious. Being aware that this is a large table I
> > imposed a shared lock on the table to prevent any concurrent updates.
> > Despite the lock and despite manually executing a global checkpoint
> > (yes, I need the keyword "global", there are 2 instances accessing the
> > DB). The lock was granted, there were no commits in my transaction nor
> > was the table updated. I checked the audit records, nothing there. How
> > is it possible? Usually, I was receiving ORA-01555 when long running
> > query was accessing table that has been concurrently modified. This
> > time, there were no concurrent updates.
>
> BTW, I am re-running now with the entire tablespace being turned to read
> only. If I get ORA-01555, I will start drinking.
>
> --http://mgogala.byethost5.com
Re-running... what? I take it you've seen Jonathan's explanation in http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e1b874c5ac59448f/91662de595de9014 I'm wondering if there could be a time-based recursion error - updates near the end of your needed data wind up in blocks that need to be cleaned out, and the (perhaps recursive) undo needed to build your view of those has been rolled off.
So often, to understand ora-1555 you have to think backwards, anything except your statement could be the real problem.
Do you see any other oddities? I take it you are tracing?
Just don't start drinking recursively. Iterate your drinking, but exit loop when I start to make sense.
jg
-- _at_home.com is bogus. Everybody wants to rule the world. http://www.signonsandiego.com/news/2010/oct/13/live-interview-twitter-co-founder-biz-stone/Received on Thu Oct 14 2010 - 10:52:10 CDT