Re: truncate randomly failing

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<HXnK9.138105$pN3.9873_at_sccrnsc03>...
> Truncate does NOT allow rollback. delete does. truncate changes the
> storage and does not "erase the blocks"

Yes, but Oracle still has to update dynamic tables to change the storage. I think that's why the symptoms are worse during heavy shared usage times AND during nightime sorta-single-user times - the latter because those times allow the processes to go fast. As the O8i reference manual says "...because the information in the V$ views is dynamic, read consistency is not guaranteed for these views." So Oracle must be internally controlling the users view of the table that is being truncated, and does not wait properly - so even selecting from v$session_wait would be misleading, since what should be there isn't (the speculation on that could be someone didn't want it to be so slow as to have to wait for the wait mechanism... :-)

Even though the locking idea may seem silly, as the truncate releases the lock, it may be worth trying in the procedure: lock, sleep 4 seconds for everything to catch up, then truncate. This as a fix would really be a byproduct, not a direct result of the lock.

As to Oracle being confused about synonyms: I've seen stuff like that too, especially with dblinks. Wish I knew why.

> Jim
> "Matthias Rogel" <rogel_at_web.de> wrote in message
> news:atcubv$12io17$1_at_ID-86071.news.dfncis.de...
> > Vladimir M. Zakharychev wrote:
> > > "Joel Garry" <joel-garry_at_home.com> wrote in message
> > >
> > >
> > >>Users view of sys.*$ is confused, because DDL is not transaction
> > >>based.
> > >
> > >
> > > Hmm... Are you saying that DDL violates ACID properties of the
> > > database? Or do I read this sentence incorrectly?
> > >
> >
> > well, what is your opinion to the following:
> >
> > as Jonathan Lewis pointed out, a truncate at first implicitly performs a
> > commit.
> > After the commit it still may fail (in case another user has
> > meanwhile performed a lock on the table)
> >
> > in my opinion, the A in ACID is violated
> > A = atomicity
> >
> > Matthias
> >
> >
> >

jg

--
@home is bogus.
There may not be anything new under the sun, but bugs hide under
rocks.