Solved

Temporary table error

Posted on 2001-06-28
4
540 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

624 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