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 - 00:11:59 CST
