Re: truncate randomly failing

Matthias : You are asking: what can go wrong in a truncate? Thats exactly my question. But something does (so we just added this loop to see if we can truncate if we try 100-times). But lets forget about the loop for a moment. The error I am getting is not the lock error (ORA-00054), but ORA-00942: table or view does not exist. So I don't see why would locking the table in the code make any difference. It really looks more like a permission problem, except that the same user can sometimes truncate the table and sometimes he can't. And this happens for different users and about 10 different tables. That's why the problem is such a mystery to me.

Daniel : Yes users should be able to truncate these 10 tables (not directly, only with the procedure call). These tables simply store the results of an algorithm, and every time it is ran the old results are truncated.
As for your questions:
Who owns the tables? How are rights to the tables granted? ... I have to check with the DBA.

Erika

Matthias Rogel <rogel_at_web.de> wrote in message news:<at40jf$10b2ku$1_at_ID-86071.news.dfncis.de>...
> Erika,
>
> what do you mean by "(the loop of 1..100 was simply added to make it
> more likely for the truncate to succeed)"
>
> what can go wrong in a truncate ?
>
> truncate first tries to put an exclusive lock on the table (correct me
> if I am wrong), then truncates it.
>
> if it is not possible to lock the table, ORA-00054 is thrown.
> ("ORA-00054: Versuch, mit NOWAIT eine bereits belegte Ressource
> anzufordern.")
>
> Please note, that Oracle doesn't wait for a table lock.
>
> Daniel: this is exactly the difference when you put the lock into the code.
> With the lock in the code, you don't get an ORA-00054.
> Instead of it, Oracle waits until the table is locked.
> Then, the truncate will succeed.
>
> Matthias
>
>
>
> damorgan wrote:
> > 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 Tue Dec 10 2002 - 08:55:02 CST