Re: truncate randomly failing

Erika wrote:

> hi,
> I am really not an expert in this (more like a beginner), but doesn't
> the truncate automatically lock the table in an exclusive mode? Why do
> you think that locking the table explicitly would help my problem?
> (the loop of 1..100 was simply added to make it more likely for the
> truncate to succeed)
> thanks
> Erika
>
> Matthias Rogel <rogel_at_web.de> wrote in message news:<at2dq3$v8c4s$1_at_ID-86071.news.dfncis.de>...
> > hi erika,
> >
> > interesting procedure.
> >
> > why don't you simply use
> >
> > procedure truncate_table (p_tname in varchar2)
> > as
> > begin
> > execute immediate 'lock table '||p_tname || ' IN EXCLUSIVE MODE';
> > execute immediate 'truncate table '||p_tname;
> > end;
> >
> > ???
> >
> >
> >
> >
> > Erika wrote:
> > > I am having some problems with truncating tables with a procedure, and
> > > I just can't figure out why.
> > > I am calling the following procedure to truncate the tables:
> > >
> > > procedure truncate_table (p_tname in varchar2)
> > > as
> > > begin
> > > for i in 1 ..100
> > > loop
> > > begin
> > > execute immediate 'truncate table '||p_tname;
> > > exit;
> > > exception
> > > when others then if (i=100) then RAISE; end if;
> > > end;
> > > end loop;
> > > end;
> > >
> > >
> > > It fails randomly (at least it seems random), for different users and
> > > different tables. The error message is: ORA-00942: table or view
> > > does not exist.
> > >
> > > It can't be a permission problem because the same table/user
> > > combination works most of the time, but fails sometimes.
> > > I wasn't able to observe any pattern of when does it happen and why,
> > > except that it seems to happen during heavy-use periods.
> > >
> > > Thank you for your help
> > >
> > > Erika

The lock should come out of the code. It is doing nothing of value.

Who owns the tables? How are rights to the tables granted?

Are you sure these users have the granted privilege to perform these truncates?

And if they do ... what is wrong with the design that they do?

Daniel Morgan Received on Mon Dec 09 2002 - 17:20:53 CST