Re: truncate randomly failing

From: Joel Garry <joel-garry_at_home.com>
Date: 13 Dec 2002 12:37:16 -0800
Message-ID: <91884734.0212131237.6d2f83e6@posting.google.com>

Matthias Rogel <rogel_at_web.de> wrote in message news:<atbvdp$vp9d0$1_at_ID-86071.news.dfncis.de>...
> Joel,
>
> I totally agree with you, I had similar ideas ...
>
> that's exactly why I suggested to *explicitly* lock the table before
> truncating it.
>
> I still think, this could be fixing it ...
>
> Matthias

I think that is going in a bit of a different direction than I was speculating:

SELECT view_definition FROM v$fixed_view_definition 2* WHERE view_name='V$ACCESS'
SQL> / VIEW_DEFINITION

select SID , OWNER , OBJECT , TYPE from GV$ACCESS where inst_id = USERENV('Inst
ance')

SQL>

SQL>       SELECT view_definition FROM v$fixed_view_definition
  2         WHERE view_name='GV$ACCESS';

VIEW_DEFINITION

select distinct s.inst_id,s.ksusenum,o.kglnaown,o.kglnaobj, decode(o.kglobtyp,

   0, 'CURSOR', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW',
5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACK
AGE', 10,'NON-EXISTENT', 11,'PACKAGE BODY', 12,'TRIGGER', 13,'TYPE',
    14,'TYPE BODY', 15,'OBJECT', 16,'USER', 17,'DBLINK', 18,'PIPE',
   19,'TABLE PARTITION', 20,'INDEX PARTITION', 21,'LOB', 22,'LIBRARY',
   23,'DIRECTORY', 24,'QUEUE', 25,'INDEX-ORGANIZED TABLE', 26,'REPLICAT
ION OBJECT GROUP', 27,'REPLICATION PROPAGATOR', 28,'JAVA SOURCE', 29,'J
AVA CLASS', 30,'JAVA RESOURCE', 31,'JAVA JAR', 'INVALID TYPE') from x$k
suse s,x$kglob o,x$kgldp d,x$kgllk l where l.kgllkuse=s.addr and

l.kgllkhdl=d.kg
lhdadr and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrfhsh and
o.kglhdadr=d.kglr

VIEW_DEFINITION

fhdl

SQL> What I'm wondering is if the actions Oracle is taking to update some x$ table that eventually gets used as V$ACCESS for the user allows some small period of time for V$ACCESS to be incorrect for Erika's views of the tables, or even V$ACCESS itself to be the ORA-942.

I was going to suggest select * from v$access when the 942 is raised, but if that is the issue, it may be resolved by the time the error-time select is done and things would be even more confusing.

Maybe someone has a trace setting to resolve this sort of thing?

>
>
> Joel Garry wrote:
> > erika_at_multimodalinc.com (Erika) wrote in message news:<766a32cf.0212120702.7a044b0d_at_posting.google.com>...
> >
> >>Thank you all for the messages.
> >
> >
> > Some wild speculation:
> >
> > Multiple users accessing a table, one of whom is attempting to
> > truncate.
> >
> > Oracle has to update sys.tab$ or sys.seg$ or something and associated
> > views.
> >
> > Users view of sys.*$ is confused, because DDL is not transaction
> > based.
> >
> > Oracle throws ORA-942 not on the user table, but perhaps on the
> > all_table view or whatever group of things Oracle is looking at to
> > determine permissions).
> >
> > So Erika, does anyone besides the truncator get the 942?
> >
> > (I've seen many odd 942 situations like this, under many different
> > scenarios. I've always [well, since 7.0, when I had a complicated DDL
> > script that would sometimes skip creating random tables] suspected an
> > Oracle bug as I've speculated, basically an improperly handled race
> > condition. Perhaps a detuned test instance set to overwork the DBWR
> > could be replicable.)
> >
> >
> >>We are testing this right now (might take as a while because we are
> >>not able to replicate this at will, so we just have to see if we can
> >>go without the error for a while). I just would like to know if
> >>anybody else ran into the same problem before. Or if yo have heard
> >>about an Oracle bug like this. I don't know the Oracle version but I
> >>will ask and post it later.
> >>
> >>Thanks again
> >>
> >>Erika
> >
> >
> >
> > jg
> > --
> > @home is bogus.
> > Time for tubby bye-bye!
> > Time for tubby bye-bye!
> > Time for tubby bye-bye!

--
@home is bogus.
Again!  Again!

Received on Fri Dec 13 2002 - 14:37:16 CST