Solved

Temporary table error

Posted on 2001-06-28
4
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

738 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