Not sure what your Yes applies to (I made several statements). But you
cannot do a rollback from a truncate. Try it.
Which is why truncate is often faster than delete. (never slower) It is
just moving the high water mark of the table and is not generating any
significant redo unlike delete. I have a program (given to me by an Oracle
consultant, I didn't;t write it) that reads Oracle data files directly and
as far as I can remember last time I used it truncate did not clear the
blocks that had data in them before the truncate occured where as delete
did.
Jim
"Joel Garry" <joel-garry_at_home.com> wrote in message
news:91884734.0212131618.643dc647_at_posting.google.com...
> "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.
Received on Fri Dec 13 2002 - 22:21:49 CST
