Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Temporary table error

I need help about Oracle error :
 
  ORA-14450
    Attempt to access a transaction temp table
    already in use.

I use global temporary tables (transaction specific).
My version of Oracle Server is 8.1.6.0.0.
0
dotlic
Asked:
dotlic
  • 2
1 Solution
 
ashokskumarCommented:
Hi,

This is may be due to a session holding uncommitted transaction in it and other session trying to do DML on it. You may verify the first transaction is completed properly by an execlusive commit or came out of the session.  Commit/rollback will release the transaction lock.

Ashok.
0
 
fvaCommented:
The global temp tables are _session_ specific, not transaction specific. The error explanation in Oracle Doc states that there's a second concurrent transaction within the same session that attempts to use the global temp table and that's not permitted.
I think you are using Autonomous transactions in the following scenario:

1. start main transaction
2. "touch" the global temp table
3. start an autonomous transaction
4. try to touch the global temp table. At this point the error appears.

If this is the case you will have to redesign the logic to avoid this situation.

F.
0
 
dotlicAuthor Commented:
To fva.
Thank you for the explanation.
I use temp tables in the following scenario:

I.step.
  In main transaction I insert into temp table TT1
  ("on commit delete rows" - transaction specific)

II.step.
  In autonomous transaction I read from temp table
  TT1, insert into permanenet table TP2 and commit this
  autonomous transaction.

III.step.
  I return to main transaction.

Do you think that I will override my problem by defining
temp table TT1 as "on commit preserve rows" - session specific temp table ?
0
 
fvaCommented:
Might be so. You can only try it.
I myself had some weird problems with "on commit delete rows" but on 8.1.5 and there are significant improvements in 8.1.6 over 8.1.5 on this issue.
Again, you can oly try it, but it seems possible. I think that the temp table is not accessible from AT because its behavior is "transaction specific" as you said and the MT is not over yet when you use the tmep table from the AT.

Good luck,
F.

P.S. Please tell us how it worked.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now