If you need to store results temporarily then don't do it the way you are
doing it. Use a global temporary table. Much simpler to code(just create
it). Truncate table deletes all the data in the table regardless of owner
and if the system is busy (the table is being used) then you can't do the
truncate. (which is why the table lock will probably work) But for what you
are doing switch to using a global temporary table, simpler, more scalable,
and designed for what you are doing.
Jim
"Erika" <erika_at_multimodalinc.com> wrote in message
news:766a32cf.0212100655.11d0eefc_at_posting.google.com...
> 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 - 09:30:09 CST
