Solved

Temporary table error

Posted on 2001-06-28
4
534 Views
Last Modified: 2008-02-26
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
Comment
Question by:dotlic
  • 2
4 Comments
 
LVL 1

Expert Comment

by:ashokskumar
ID: 6235709
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
 
LVL 4

Expert Comment

by:fva
ID: 6237801
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
 

Author Comment

by:dotlic
ID: 6239218
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
 
LVL 4

Accepted Solution

by:
fva earned 200 total points
ID: 6243985
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now