Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: truncate randomly failing

Re: truncate randomly failing

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Thu, 12 Dec 2002 15:11:58 GMT
Message-ID: <2H1K9.124636$pN3.9254@sccrnsc03>


Global temporary tables are not really temporary. The results they store are. Are you saying you need to have the results extend beyond the user's session?(for someone else)
Jim
"Erika" <erika_at_multimodalinc.com> wrote in message news:766a32cf.0212120702.7a044b0d_at_posting.google.com...
> 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:11:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US