Re: truncate randomly failing

Thank you all for the messages.

Some answers to your questions:

Matthias: the loop is definitely not the problem, because the problem was there first and then we added the loop to see if it fixes the problem, it didn't fix it but it definitely didn't cause it. So really, forget about the loop, the problem was happening when there was no loop in the procedure and continues happening with the loop. And no, I never got the ORA-00054 error.

Jim and Vladimir: the tables can't be temporary, they need to survive the end of the session, and only should be truncated if someone (possibly a different user) runs the same algorithm and decided to override the results of a particular run. There was a reason why things were designed the way they were, so that is really not the question here. The question is that the way the truncate is implemented it shouldn't fail, but it does, and most likely it is somehow related to synonyms.
I am copying here what the DBA thinks is happening:

"ACT_OWN owns the tables. And ACT_OWN owns the truncate_table
procedure.
ACT_OWN has granted the "execute" authority to the various users who need
it. When the truncate_table procedure is executed by user A, it is run with
the privileges of the owner - ACT_OWN. Therefore, we should never get the
ORA-942 error. But we do. I believe we have run into an intermittent bug.
Public synonyms have been created for every table. The public synonym means
that when user A refers to the name "mytable" it is translated to
"act_own.mytable" and then the permissions are checked on the
table. Synonym translation is valid for DML (select, insert, update, delete), but not for DDL (create, alter, truncate, etc). Since the truncate
command is being issued by ACT_OWN against and ACT_OWN table, synonyms should not be involved, but I think the bug is that sometimes Oracle tries
to resolve the table name via synonyms and fails - thus ORA-942 (table not
found).
My recommended workaround is to modify the truncate table procedure to explicitly state the owner in the truncate command, eliminating any attempt
at synonym resolution:

    execute immediate 'truncate table act_own.'||p_tname; "

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 Received on Thu Dec 12 2002 - 09:02:57 CST