2 Replies Latest reply: Nov 29, 2012 3:40 PM by rp0428 RSS

    ORA-14450 attempt to access a transactional temp table already in use

    877720
      I have a stored procedure, sql is written like this:

      insert into temptable xxxx;
      select xx from temptable;
      commit;

      here temptable is a global temporary table.


      there is nothing else about this temptable in my procedure, only the insert and select statement.


      but when I start my application to make some concurrency calls on this procedure, it throws an error

      ORA-14450 attempt to access a transactional temp table already in use

      I can't understand since a temp table is used per session, one session's insert and update will not affect others, and I'm not using any DDL statement, why it comes this error?


      By the way, my oracle version is 10.2.0.4 and running RAC on 2 nodes.
        • 1. Re: ORA-14450 attempt to access a transactional temp table already in use
          Osama_Mustafa
          ORA-14450:attempt to access a transactional temp table already in use
          Cause: An attempt was made to access a transactional temporary table that has been already populated by a concurrent transaction of the same session.
          Action:do not attempt to access the temporary table until the concurrent transaction has committed or aborted.


          I think if you post proc it will be best .
          • 2. Re: ORA-14450 attempt to access a transactional temp table already in use
            rp0428
            >
            I have a stored procedure, sql is written like this:

            insert into temptable xxxx;
            select xx from temptable;
            commit;

            here temptable is a global temporary table.

            there is nothing else about this temptable in my procedure, only the insert and select statement.
            >
            If that is really true then your stored procedure won't be valid and you can't even run it. Please post the actual procedure code.

            You can't have a SELECT in a procedure with an INTO clause of some sort or the compiler will give you an error. Oracle has to have someplace to put the data.