Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Temporary table error

Posted on 2001-06-28
4
Medium Priority
?
542 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 600 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

722 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