altering global temp table
A reader, October 25, 2001 - 6:32 pm UTC
Iam trying to add a column to a global temp table
but Iam getting the error
[1]: (Error): ORA-14450: attempt to access a transactional temp table already in use
I dont see that it is already in use, Iam not using it in any of my sessions, in my current session too , I deleted the rows and committed.
How should I see if the table is being used by some other session.
How should I alter the global temp table and add a column to it.
thank you
October 25, 2001 - 7:34 pm UTC
Well, you don't say HOW you created it (on commit delete rows/preserve rows)
If it was preserve rows -- you were doing it to yourself. Regardless, you can use v$lock to see who's got it:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create global temporary table t ( x int ) on commit preserve rows;
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1 );
ops$tkyte@ORA717DEV.US.ORACLE.COM> select * from v$lock
2 where id1 = ( select object_id from user_objects where object_name = 'T' );
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
8423EC5C 8423EC6C 12 TO 21884 1 3 0 21 0
844A2EC4 844A2ED8 12 TM 21884 0 3 0 21 0
<b>That shows that sid 12 (us) has it...</b>
ops$tkyte@ORA717DEV.US.ORACLE.COM> delete from t;
ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
ops$tkyte@ORA717DEV.US.ORACLE.COM> select * from v$lock
2 where id1 = ( select object_id from user_objects where object_name = 'T' );
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
8423EC5C 8423EC6C 12 TO 21884 1 3 0 43 0
<b>this shows that we STILL got it -- even after the commit -- due to the "preserve rows" -- if this had been "delete rows", nothing would be in v$lock...</b>
ops$tkyte@ORA717DEV.US.ORACLE.COM> alter table t add y int;
alter table t add y int
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
<b>the solution at this point would be to disconnect/connect. In the event other sessions have it -- you have to KILL them or wait.</b>
on commit delete or preserve rows
naga, August 05, 2002 - 2:52 pm UTC
can i know the difference between on commit delete or preserve rows. as it self explanative i know what it does. but i am interested in knowing what oracle interenally does.
which one is recommended..and why is that?
thanks
August 06, 2002 - 8:10 am UTC
we either TRUNCATE the temp segment you allocated when you COMMIT (on commit delete rows) or we TRUNCATE the temp segment you allocated when you logoff (on commit PRESERVE rows)
It just changes when we truncate.
Neither is better then the other. You need to ask yourself -- do I need this data for the TRANSACTION (on commit delete) or for my entire session (on commit preserve)
a Reader
Larry Liang, January 14, 2003 - 11:39 pm UTC
Hi Tom, I still could not fully understand the on commit delete rows for transaction duration. Does that mean all the rows are gone when you insert into a temp table for some kind of use for a transation immediately after commit the insert into the temp table? If the records are gone, it means no data could be retrieved for your transaction. I know it is a silly question, and something is certainly wrong. Please correct me.
Thanks,
January 15, 2003 - 8:06 am UTC
on commit delete rows means when you commit -- rows go "poof", disappear.
so,
insert into global_temp_table select ....
commit;
results in empty table. You would use this when the data in global_temp only needs to be used during the course of your transaction -- like a staging table (load up tmp, process tmp into other tables, commit -- tmp goes away)
A Reader
Larry Liang, January 16, 2003 - 12:47 am UTC
Thanks for your reply Tom.
From your point, when you insert into a temp table, the records inserted will be available for use without issuing a commit if I understand the logic correctly. After you manipulate the records inserted, and then commit just to delete the records (transaction duration).
If I get your point, this behavior is totally different from permanent tables. You won't see the records inserted into a permanent table until after you issue a commit.
Please let me if I am right or wrong.
Thanks again!
January 16, 2003 - 8:34 am UTC
you are wrong.
you are right on the first point. you insert -- you see them -- you commit and they disappear (assuming on commit delete rows)
you are wrong on the second point. A transaction HAS ALWAYS BEEN ABLE TO SEE MODIFICATIONS IT MAKES.
No other session can but that session can most certainly see its changes.
the behaviour for permanent and temporary tables as far as visibility goes is identical.
What for?
Anirudh, January 16, 2003 - 5:21 am UTC
Hi Tom,
As you stated:
" this shows that we STILL got it -- even after the commit -- due to the
"preserve rows" -- if this had been "delete rows", nothing would be in
v$lock..."
What is the significance of keeping it locked. Why is that done?
Thanks.
January 16, 2003 - 8:48 am UTC
you have a temporary segment -- you are using the object. It matters not (heck we don't even KNOW) that it happens to be empty via "delete from t" -- as far as we are concerned -- there is data in there.
We cannot tell that a table is empty after a delete. We don't track that sort of stuff. Hence the temporary table is still "in use"
I got it, THANKS..
Anirudh, January 16, 2003 - 8:42 am UTC
Altering global temporary table
Abuaker Khered, April 24, 2003 - 5:45 am UTC
Hi,
Can I do:
create global temporary table gtt_customer
as select * from customer
where 0=1
on commit delete rows
I know it is possible to:
create global temporary table gtt_customer
as select * from customer
where 0=1
but can I add " on commit delete rows " to the command, or alter the table afterward.
Thanks and Regards,
April 24, 2003 - 8:15 am UTC
ops$tkyte@ORA920> create global temporary table t
2 as
3 select * from all_users where 1=0
4 on commit delete rows;
on commit delete rows
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
ops$tkyte@ORA920> edit
Wrote file afiedt.buf
1 create global temporary table t<b>
2 on commit delete rows</b>
3 as
4* select * from all_users where 1=0
ops$tkyte@ORA920> /
Table created.
ops$tkyte@ORA920>
it simply goes BEFORE the subquery
Thanks
A reader, April 26, 2003 - 3:22 am UTC
Thanks,
Abuaker
Problem with Global Temp Table
Bharath, May 19, 2003 - 6:09 pm UTC
We have a problem, when we join the Global temp table col with the table at the remote site.
For e.g(Not the actual sql)
Select X from remote_table,global_temp_table
where remote_table.deal_id = global_temp_table.deal_id
It takes 360 sec to retrieve all the record but if i replace the temp table with the array like
(remote_tablein(Array_var)) then it retrieves in 2 sec.
The reason we went with Temp Table to avoid 1000 limitation in the IN variable.Is there any thing that we can do speed up the process by using global temp table.
May 20, 2003 - 7:27 am UTC
try using the driving_site hint to have the query optimized at the other location or put the gtt ON the remote site.
Problem with Global Temp Table
Bharath, May 20, 2003 - 9:57 am UTC
>>put the gtt ON the remote site
We dont have a control on it.
I read somewhere that RuleBased Optimizer will not understand the Index on the remote site,so it will always go for Full table scan,Is that true?
By Giving driving site hint, will the query will use the Index on the remote site?
May 20, 2003 - 1:13 pm UTC
try it -- see what happens.
Problem with Global Temp Table
Bharath, May 20, 2003 - 3:42 pm UTC
I tried with driving_site in hint in the sql i saw a slight improvement in the response time,but large diference in
recursive calls, db block gets,consistent gets, physical
(Response time No Hint:364 Sec)
((Response time with Hint):310 Sec)
Statistics(Without Hint)
----------------------------------------------------------
16 recursive calls
28 db block gets
7 consistent gets
395 physical reads
0 redo size
3022 bytes sent via SQL*Net to client
379 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
2 sorts (disk)
21 rows processed
Statistics(With the Hint)
----------------------------------------------------------
4 recursive calls
4 db block gets
1 consistent gets
1 physical reads
0 redo size
3002 bytes sent via SQL*Net to client
379 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
Will the distributed query optimization will work better in CBO rather in RBO
May 21, 2003 - 7:23 am UTC
it would appear, based on the above, that the query is being optimized now on the remote site.
Tell me this -- if you run the query ENTIRELY on the remote site, what is the performance there. You need to find out what the "best possible case in the world" is before going further there.
Problem with Global Temp Table
Bharath, May 21, 2003 - 9:14 am UTC
When we run the entire query in remote site without joining the Global temp table by passing an array is very fast we are getting the entire result set in 5 sec.
It's Look like under RBO remote table is accessed first and then the local table bcos of this, predicates(e.g
remote_table.sec_id in(select local_table.sec_id from
local_table))which will reduce the result from the remote table is not used and remote table is going for Full Table Scan.
May 21, 2003 - 9:41 am UTC
no, what I mean is -- put the gtt on the remote site and run the EXACT SAME THING only 100% locally.
Problem with Global temp table
Bharath, May 21, 2003 - 11:25 am UTC
We dont have a write privilege in the remote database that's the main reason we cant create the GTT on the remote site.
Is this True?
It's Look like under RBO remote table is accessed first and then the local table, bcos of this, predicates(e.g
remote_table.sec_id in(select local_table.sec_id from
local_table))which will reduce the result from the remote table is not used and remote table is going for Full Table Scan.
May 21, 2003 - 2:10 pm UTC
just *test* it -- ask the DBA on the remote site to *help* you test something quick.
They should be dying to help you -- it is their system you are hitting hard.
Problem joining multiple tables remotely
Bharath, May 21, 2003 - 4:37 pm UTC
Yes i tried executing GTT in remote, Query returned the output very fast.Unfortunately we can't take route.If every thing happens remotely query response in fast.
I am currently tesing the query with CBO ,result dosent look promising???
May 21, 2003 - 5:09 pm UTC
well -- i would really question "why, why cannot i take that route". does the remote dba want you pounding on his system? I don't get it?
Yes Tom I Understand it
Bharath, May 21, 2003 - 6:31 pm UTC
>>"why, why cannot i take that route"
Yes i know that as a techincal guy i should have followed this route but due to some business restriction(we have to convince lot of intermediate managers).we are not able to do this.But we have overcome this problem by changing GTT to String array(i.e IN).
But i was thinking that CBO will solve my problem by reading an article from metalink but it's look like i am getting the same response from CBO oracle isntance similar to RBO.
reader
A reader, May 22, 2003 - 12:34 am UTC
"
8423EC5C 8423EC6C 12 TO 21884 1
"
What is lock type "TO" and ID2 for "TO" Is it documented
May 23, 2003 - 7:49 am UTC
TO is the temporary object enqueue. id1 is the object number of the temporary object. The purpose of the lock is to prevent DDL from being issued against an object while DML is also occurring.
Another question
ZS, May 23, 2003 - 1:23 am UTC
Tom,
Can you please clarify one point:
"we either TRUNCATE the temp segment you allocated when you COMMIT (on commit
delete rows) or we TRUNCATE the temp segment you allocated when you logoff (on
commit PRESERVE rows)
"
1. does oracle really do TRUNCATE or DELETE?. and if TRUNCATE, how does affect other users using the same table at the same time?
2. Also, is there any fragmentation/high water mark pointing to the last used block when we delete and causing performance degradation or this does not happen in temporary table.
Thanks,
ZS
May 23, 2003 - 8:43 am UTC
1) it is functionally equivalent to truncate.
each user gets their own temp segment, they are not overlapping or interleaved at all. they are 100% concurrent and separate.
2) no there is not. well, not really.
of course you could:
insert 1,000,000 fat rows into a global temp table.
delete it yourself
then, until the truncate kicks in -- you will full scan all of the blocks. But ONLY you -- no other session will even consider touching your blocks.
Sorry
ZS, May 23, 2003 - 1:25 am UTC
Sorry I just realized that I should not ask questions here.
Thanks alot
ZS, May 25, 2003 - 9:00 pm UTC
Can GTT be used in recursive calls to procedure???
Suvarna, August 21, 2003 - 9:53 am UTC
Hi Tom,
A call to procedure modifies some values and I need these values in next call to the same procedure rather than the actual table values. For this I am using a GTT. I have set on commit preserve rows option, but I find nothing written in the global temp table. If GTT is replaced with a regular table, it works fine. Not able to figure out why?
August 21, 2003 - 7:03 pm UTC
give us a simple, small, concise, self contained test case to demonstrate the issue.
I don't see what the problem is yet...
altering global temporary tables
Yogeeraj, September 04, 2003 - 8:29 am UTC
Dear sir,
I have case where on our production database a table was created with the "on commit delete rows" clause instead of "on commit PRESERVE ROWS"
The "alter table" command is not working :
===========================================================================
yd@ORA817.MU> CREATE GLOBAL TEMPORARY TABLE T_ORDERS (
ORDERNO VARCHAR2 (8),
JOBNO VARCHAR2 (6),
COLOUR NUMBER (2),
GMTSEQ VARCHAR2 (1),
EXPQTY NUMBER (6)) ON COMMIT DELETE ROWS;
Table created.
Elapsed: 00:00:00.03
yd@ORA817.MU> alter table t_orders on commit delete rows;
alter table t_orders on commit delete rows
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Elapsed: 00:00:00.01
yd@ORA817.MU>
===========================================================================
The documentations does not mention anything about it....
</code>
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem2a.htm#2054899 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem3e.htm#2061078 <code>
Do we have "drop and recreate the table" as the only solution?
thank you in advance for your reply.
best regards
Yogeeraj
September 04, 2003 - 9:59 am UTC
seems so, yes.
Temporary table for Report???
Shahadat, October 18, 2003 - 3:46 am UTC
Dear Tom
I have created a temporary table for report runing but its not working.After inserting data in a temporary table by using complex query,now i wanted it to generate a report on the basis of temporary table.
But when i generate report,no data in the report runtime but in pl/sql it shows well.
What's the problem.
--version
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
CREATE GLOBAL TEMPORARY TABLE GTEMPREGHIS (
BIN VARCHAR2 (10),
---------------------------------------
TAXPAYER_DES VARCHAR2 (65))on commit DELETE rows;
October 19, 2003 - 5:54 pm UTC
no idea -- given that "plsql can see it", you have to understand that "it was there"
perhaps the report generating tool you are using (which is not named) is doing a commit and you have "on commit DELETE rows" -- most likely culprit here.
not working
A reader, October 19, 2003 - 11:37 pm UTC
Hi Tom
It's not working whatever on commit/on commit delete
.
Report Builder 6.0.8.8.3
create global temporary table
t (x int,y varchar2(2));
whatever on commit/on commit delete
what should i do?does it mean that GTT is only for plsql?I created view by using GTT,it did not worke as well.
October 20, 2003 - 8:19 am UTC
are you populating the temp table IN THE REPORT or in another session?
TO: Shahadat from Bangladesh
Jens, October 20, 2003 - 4:15 am UTC
I do the same with Oracle Reports and it works fine. I defined an after-parameter-form-trigger where i start the procedures to fill the global temp. tables.
Are you sure, that you use the same session for filling the GTT and running the report?
GTT problem reply
Shahadat, October 20, 2003 - 11:52 pm UTC
Hello Tom/Jens
Thanks for reply.I know GTT works in plsql/reports trigger individually.I also know that whenever session changed it will not work.
But i wanted GTT by manipulating from Forms6i and then show the manipulation result in the report6i.But its not working.
Normally we manipulate from forms then insert it into permanet table(otherwise we use view) and then report.I wanted to replace this permanent table by GTT.Is it possible?Please give me advice.
October 21, 2003 - 7:14 am UTC
you switched sessions.
you fill it in forms (session 1)
you try to read it in reports (session 2)
you need to
a) fill it in reports
b) read it in reports
that is the ONLY way.
GTT v normall tables
kit, April 08, 2004 - 10:12 am UTC
HI Tom,
I can see the benefit of GTT when used as a staging area during a process. but why would you create GTT and preserve the rows rather than just create a normall table
thanks
April 08, 2004 - 10:53 am UTC
because you wanted the data to persist for your session? based on your needs, you might need it to persist for the SESSION, not just a transaction.
temporary tables
kit, April 08, 2004 - 12:06 pm UTC
Little bit confused.
my understanding is a below
session has many transactions
if declare a GTT preserve rows on commit and batch component executes sql that then inserts rows into it. on exit of that session it will do an implicit commit. if then another batch process then reads from the table, it will be the same data as inserted in the previous session
my questions is that any benefit of using GTT than a normall table
April 08, 2004 - 3:27 pm UTC
on commit preserve rows is SESSION BASED..
On exit of that SESSION, the table data goes "poof"
No other session will ever see the data in that GTT.
global temporary table and connection pooling
A reader, May 16, 2004 - 9:22 am UTC
Tom,
Will a global temporary table work with the option on commit preserve rows in a the following situation
1) Connection pooling present on the Oracle application server.
2) Dedicated connection between application server and the databse.
Since connection pooling is present, the application server say has already 10 connections in the connection pool. If client1(IE browser) connects, the client will be given one of the connections from the connection pool. Other clients may also be given the same connection as client1. In such a situation will the global temporary table work with the option on commit preserve rows work - same connection different session/same session(?)..
If we change #2 above from dedicated connection to shared connection, how will things change.
Thank you
May 16, 2004 - 10:37 am UTC
will it "work", technically yes.
will it "work", as in behave in the way you probably want, NO
you would never be assured of rejoining your connection that can see your data. Your data would become visible to some other session right after you let go of the connection.
global temporary table and connection pooling
A reader, May 16, 2004 - 12:03 pm UTC
Tom,
Would like your comments on the argument below
After the client(say client1) opens and closes a connection, the connection would go back to the connection pool and would be given to next client that asks for the connection - right(?). Before client1 closes the connection, won't all sessions for that client be closed and so global temporary tables should work with the option 'on commit preserve rows'
Thank you
May 16, 2004 - 5:04 pm UTC
the goal of most connection pools is to keep the sessions open, quite the opposite of what you state.
if session1 grabs a connection and changes something in that 'session', in all likelyhood, session2 will inherit that stuff.
Global temp table and connection poolong
A reader, May 17, 2004 - 8:49 am UTC
Tom,
Thank you for that clarification
So in a a connection pool if there are 10 connections, each connection could have n sessions and all these 10 connections and 10 x n sessions are kept open, but the transcations closed - right?
Thank you
May 17, 2004 - 9:24 am UTC
if there are 10 connections -- there would TYPICALLY be 10 sessions. You share sessions.
question on GTT
A reader, September 23, 2004 - 2:58 pm UTC
does it make sense to add "monitoring" clause
on a global temporary table?
September 24, 2004 - 9:28 am UTC
not really
Whay the rows are still there after I did "commit_form"?
A reader, October 15, 2004 - 6:07 pm UTC
Hi, Tom,
I created the global temp table and on commit delete rows.
But after the whole process finishes, the rows are still there after I did "commit_form" in the form to finalize the process. Is it a bug or I miss something?
Thanks,
October 15, 2004 - 6:15 pm UTC
does commit_form() only commit if something in the form "needed committing"? cannot remember -- but probably so (eg: it would not commit unless it felt it needed to)
try
post_form;
commit;
Global temporary table issue
A reader, October 20, 2004 - 6:57 pm UTC
Dear Tom,
We have a global temporary table created with on commit delete rows. This table is used in a stored procedure which is called from a Java application utilizing connection pooling. Once in a while we get the following exception:
java.sql.SQLException: ORA-14450: attempt to access a transactional temp table already in use
Reading Oracle documentation and your explanations here it sounds to me like this could happen when the temp table is created with the preserve rows option, but not with delete rows option. Do you have any ideas what could cause this?
(We are using Oracle 9.2.0)
Thanks in advance.
October 20, 2004 - 9:06 pm UTC
"transactional", eg: not session (not preserve rows)
ops$tkyte@ORA9IR2> create global temporary table t ( x int ) on commit delete rows;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1);
1 row created.
ops$tkyte@ORA9IR2> declare
2 <b> pragma autonomous_transaction;</b>
3 begin
4 insert into t values ( 1 );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at line 4
anything like that going on?
Global temporary table issue
A reader, October 21, 2004 - 10:19 am UTC
Dear Tom,
Thanks so much for your followup. We do not use autonomous transactions in our code. Our Java code gets a db connection out of the connection pool, executes a stored proc and then commits or rolls back. The connection is then returned to the pool. Do I understand correctly that in this setup the only way for this exception to occur is if 2 threads try to access the same temp table at the same time via the same database session? So basically somehow we have more than one transaction open on the this temp table from the same session... Also, how would you try to find something like this?
Thanks in advance.
October 21, 2004 - 2:44 pm UTC
are you sure you are not using autonomous transactions anywhere?
two threads would not do this, you need two transactions in the same session.
Global temporary table issue
A reader, October 22, 2004 - 12:50 pm UTC
Dear Tom,
Thanks again for your followups.
I am sure we are not using autonomous transactions. I have another question though, would there be anything we need to know if the stored procedure which inserts the rows into the temp table also participated in a remote transaction (via db links)? I noticed there is something in the documentation saying that global temp tables are not supported with distributed transactions. I am assuming this means you can't access a global temp table remotely and nothing else.
Thanks in advance.
October 23, 2004 - 9:30 am UTC
I'm not able to reproduce the issue in a distributed environment myself.
I'll recommend you contact support, they can tell you how to set a debugging event to generate a big stack trace with you hit this error and they can look at that to try and determine "what was going on"
Prasad, October 28, 2004 - 4:30 am UTC
ORA-14450
A reader, March 10, 2005 - 1:42 pm UTC
Tom,
is there any other case that you are aware of for getting ORA-14450? We are periodically getting this error and we DO NOT drop/alter temporary table and DO NOT use autonomous transactions. I cannot provide you with persistent test case because error is random. Just some details on environment:
DB B (8.1.7) has global temporary table with delete rows on commit. DB A (9.2.0.5) issues insert/delete/select over DB link to modify/query this table (as well as other tables in DB B). When other tables are updated, triggers on these tables in DB B query data from temporary table and use it for storing log.
In 99% of time system is working fine.
March 10, 2005 - 7:21 pm UTC
nope, but work with support and they can set an event to get a detailed error stack to help diagnose what is wrong.
Global Temporary Table
veeresh, March 15, 2005 - 1:57 am UTC
Hi Tom,
I have created global temporary table with on commit preserve rows.
My doubt is
After insertion into global temporary table from one session. I can not able to view the records from different session. (I think global temporary table works for that specific session.)
How i can view the records from another session?
If not possible then ,what is the use of on commit preserve rows Option provided by oracle?
March 15, 2005 - 7:59 am UTC
you cannot -- that is the entire premise, the sole reason for being -- of a global temporary table.
to give you a session specific temporary set of data.
on commit preserve rows is to make the data persist for YOUR SESSION
as opposed to
on commit delete rows to make the data persist for your CURRENT TRANSACTION
global temporary table is replacement for objects
riyaz, March 16, 2005 - 12:13 am UTC
Gr8 answer in nut shell.
I am using global temporary table in great extend. (Whenever it is not achievable with single SQL statement and depends upon the situation, I prefer to use global temporary table)
Since I used global temporary table extensively, now a days I started not using (almost skipping) objects (varray or table). Is this ok strategy or I am doing wrong? Please give your views.
March 16, 2005 - 8:18 am UTC
it is almost always possible to to it in a single sql statement. I've used a gtt once in real life -- and it was to store inputs from a program to be processed.
ORA-14450 error
A reader, April 22, 2005 - 3:14 pm UTC
Tom,
recently found this explanation of ORA-14450 error:
ORA-14450: attempt to access a transactional temp table already in use
Cause: An attempt was made to access a transactional temporary table that has been already populated by a concurrent transaction of the same session.
Action: Do not attempt to access the temporary table until the concurrent transaction has committed or aborted.
What does "access" mean? Select? Insert/Update/Delete? Tried everything and wasn't able to reproduce...
Could you please help me to build a test case to reproduce this explanation? Thank you very much for all you are doing!
April 22, 2005 - 3:45 pm UTC
common cause, autonomous transactions (sub transactions)
ops$tkyte@ORA9IR2> create global temporary table gtt ( x int ) on commit delete rows;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into gtt values ( 1 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 PRAGMA autonomous_transaction;
3 begin
4 insert into gtt values ( 2 );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at line 4
ORA-14450
A reader, April 22, 2005 - 4:17 pm UTC
Thank you!
I'm pretty sure there is no autonomous transactions on DB side. Is it possible to have something like this if middle tier is configured to use session pool? Sorry - I do not know a lot about middle tier, more on DB side...
April 22, 2005 - 4:33 pm UTC
tell me more, anything "fancy" about the connection pool, what connection pool, what is the client written in, distributed transactions happening?
ORA-14450
A reader, April 22, 2005 - 4:49 pm UTC
Web server - Apache. Connection pool is configured for 10 connections max using the same DB account for database A. Database A has dblink to database B, where this temporary table sits. Actual SQL is issued in DB A, but it updates/inserts/deletes records in table in DB B. ORA-14450 happens may be twice a month. When it happens, it looks like one of the sessions from the session pool is not released back and still be shown as active. It also looks like problem happens after some other error occurs - for example, foreign key constrain violation.
Global Temporary table created in schema 1 used in schema 2
Maryam, June 02, 2005 - 2:29 pm UTC
Tom,
I have a scenario:
In schema1 a GTT is created and used in a PROC (proc1).
In schema2 a PROC(proc2) is calling schema1.proc1 (gtt is accessed here).
When a user logs into schema2 and calls proc2, where is the GTT at the time of execution? in temporary tablespace of schema1 or schema2?
Thank you for your feedback
June 02, 2005 - 5:18 pm UTC
if the gtt hasn't been used, eg:
connect
exec schema1.proc
the gtt will use schema1's temporary tablespace.
if the gtt has been used, eg:
connect
insert into schema1.gtt....
exec schema1.proc
it'll be in schema2's temporary tablespace
first "current schema" to touch it decides.
Global Tempprary table which temp tablespace?
Maryam, June 02, 2005 - 6:28 pm UTC
Your answer was brilliant. I just tested it and found it to be true.
Thank you again for your great response and quick turn around.
I am very grateful to your answer.
--M.T
GTT in Oracle Reports / APPS 11i
Chris Seymour, July 01, 2005 - 11:25 am UTC
I have a followup question to the question about GTT's in reports. We are launching reports via Apps 11i as concurrent requests. One report needs to use pl/sql to insert into a table, and then in that same report read from the table.
My question is should we be using a regular table, and committing/truncating in the report, or use a GTT? I know each concurrent request is a separate session, but I am confused at what happens when multiple users try to access the table at the same time.
July 01, 2005 - 12:08 pm UTC
if they each have their own session, a session specific (on commit preserve rows) gtt would be usable -- gtts are designed to be highly concurrent, it is as if each user has their own (they do)
ORA-06519 with global temporary table
Jairo Ojeda, July 04, 2005 - 7:40 pm UTC
Tom, I have a handled error package that insert in a log error table using PRAGMA AUTONOMOUS_TRANSACTION.
I'm coding a procedure that insert in a global temporary table (on commit preserve rows), then raise a RAISE_APPLICATION_ERROR using the handled error package. Then I need to query the inserted rows, but the temporary table is empty.
Any idea to preserve the rows,
July 05, 2005 - 7:24 am UTC
ops$tkyte@ORA9IR2> create global temporary table gtt ( msg varchar2(40) ) on commit preserve rows;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure log_error( p_msg in varchar2, p_errcd in number )
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into gtt values ( p_msg );
6 commit;
7 raise_application_error( p_errcd, p_msg );
8 end;
9 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec log_error( 'testing', -20001 );
BEGIN log_error( 'testing', -20001 ); END;
*
ERROR at line 1:
ORA-20001: testing
ORA-06512: at "OPS$TKYTE.LOG_ERROR", line 7
ORA-06512: at line 1
ops$tkyte@ORA9IR2> select * from gtt;
MSG
----------------------------------------
testing
ops$tkyte@ORA9IR2>
The issue is with the raise
Jairo Ojeda, July 05, 2005 - 11:04 am UTC
bnfinversion@VENUS> CREATE GLOBAL TEMPORARY TABLE gtt ( msg VARCHAR2(40) )
2 ON COMMIT PRESERVE ROWS;
Table created.
bnfinversion@VENUS> CREATE TABLE log_error(msg VARCHAR2(40));
Table created.
bnfinversion@VENUS> CREATE OR REPLACE PROCEDURE sp_log_error
2 ( p_msg IN VARCHAR2, p_errcd IN NUMBER )
3 AS
4 PRAGMA autonomous_transaction;
5 BEGIN
6 INSERT INTO log_error VALUES ( p_msg );
7 COMMIT;
8 RAISE_APPLICATION_ERROR( p_errcd, p_msg );
9 END sp_log_error;
10 /
Procedure created.
bnfinversion@VENUS> DECLARE
2 lv_msg VARCHAR2(40) := 'testing';
3 BEGIN
4 INSERT INTO gtt VALUES ( lv_msg );
5 sp_log_error( lv_msg, -20001 );
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-20001: testing
ORA-06512: at "BNFINVERSION.SP_LOG_ERROR", line 8
ORA-06512: at line 5
bnfinversion@VENUS> SELECT * FROM gtt;
no rows selected
--If I comment the line 8 on sp_log_error I can select data from the gtt, but I need the raise to tell the user something like "You need to verify the data before continue"
July 05, 2005 - 11:33 am UTC
you are very much confusing me here. Of course the insert disappears (it would in informix too).
I don't get it.
Can I use somehow the raise application error as above?
Jairo Ojeda, July 05, 2005 - 12:51 pm UTC
Ok, this is the case,
We need to insert on a table rows retrieved from a table on other database --we are in 9iR2, and we need data from a 7.3.4 database
I have a procedure that get the rows to insert on my table from the 7.3.4 database, my table is a child one so if I get the ORA-02291, I insert this row(s) in the global temporary table and send a message through the handled error package to the end user like "You need to verify the data before continue" and the end user should query the temporary table include the parent row(s) before continue.
As a solution,
In a test env. I use an out parameter on my procedure where I get
0 if the transaction is fine,
1 if rows inserted in the global temporary table
and the programmer check the out parameter and send the message to the end user.
Can I use somehow the raise application error as above?
July 05, 2005 - 1:14 pm UTC
yes, you could use an autonomous transaction in another procedure to save the data, as I demonstrated a couple of pages up ago in the very beginning.
put the logic to save the rows in the gtt into an autonomous transaction -- but this logic looks really "whacky", not sure I'd want to implement or be responsible for documenting/maintaining such a structure.
You right!
A reader, July 05, 2005 - 1:30 pm UTC
To Jairo Ojeda
Jonathan Kroeker, July 05, 2005 - 4:40 pm UTC
First let me state that I'm not endorsing your approach to error handling. It sounds a little dubious to me but then I don't really have all the details...
The issue of your entry not showing up in the global temporary table after the raise_application_error is caused by Oracle's normal handling of exception situations. When an error occurs (and an exception you raise is included in this category) the currently executing statement is rolled back. In your case, I think that will take you back to just before your anonymous PL/SQL block began. If you want to avoid this, insert a commit statement after the 'INSERT INTO gtt' and before the call to sp_log_error. Another option would be to try to explicitly limit the scope of the automatic rollback by using savepoints or something similar.
Connection pooling and Global temporay table
Tony, October 19, 2005 - 11:45 am UTC
Tom,
Thanks a lot for your all your help and time.
I'm trying to use global temp. tables to improve the perf. of some querying using a long IN list. On the application side weblogic is doing connection pooling and I'm concerned if multiple users sharing the same Oracle session will have any conflict with the data in the global temp. table.
Can "on commit delete rows" in this case save me. As I understand "perserve rows" is session level and "delete rows" is transaction level, if that is true I think it should be safe to have "on commit delete rows" for connection pooing.
If I'm wrong can you suggest a strategy where the 2nd user waits for the temporary table to clear up (commit)
Thanks
God bless
October 19, 2005 - 12:42 pm UTC
if you have a global temporary table with on commit delete rows and
a) grab connection
b) populate temp table
c) run query
d) COMMIT;
e) give back connection
no problem.
Global temporary table in Oracle reports
Karthikeyan Subrmanian, December 06, 2005 - 4:51 am UTC
Got an overall idea on GTT.
Global temporary table in Oracle reports
Karthikeyan Subramanian, December 06, 2005 - 4:54 am UTC
While inserting into the GTT, will there be any entry into the log file?
Is it possible to create an Index for GTT as Iam using the GTT as the main query in Oracle reports?
December 06, 2005 - 6:00 am UTC
yes, you may index them.
yes, there will be redo generated because there is undo generated and redo is always generated for the undo.
redo for the temporary segment - no, redo for the undo generated in support of rollback and consistent read queries - yes.
Usage of temporary tablespace by Global temporary tables
Karthikeyan Subramanian, December 12, 2005 - 4:45 am UTC
Suppose if I am using more than one temporary table in the same database at the same time, will the performance be reduced?
If so, How to overcome this problem?
December 12, 2005 - 8:00 am UTC
well, the use of temporary tables in general typically means "you might not be doing something right, you might be trying to just 'port' a sqlserver application to oracle without considering that the two databases are very different".
It is not that using two temporary tables is "slow"
But it could be that you think you need to use temporary tables implies your approach isn't the "best" in some cases.
But not having any example, cannot comment any further.
urgent requirement
satya tulasi, December 29, 2005 - 4:33 am UTC
Hi,
We r searching for one thing in oracle from so many days but not yet clear till now that is 'How to store image in oracle database'? I got something in ur answers that have to create canvas but iam not clear all those things.So pls make me clear.
Distributed transactions are not supported for temporary tables.
A reader, January 12, 2006 - 3:39 pm UTC
Dear Tom,
can I ask you a question on this? What does it exactly mean? Let's say we have DBserver1 and some temporary tables are implemented and used in stored procedures on this server. Later DBserver2 was created and there is some code on this DBserver2 that calls procedures from DBserver1 over a DBlink. Does this restriction mean that it is not allowed at all due to that fact that gtt are used in DBserver1? What is exactly not supported?
Thank you very much for your regular help!
Distributed transactions are not supported for temporary tables.
A reader, January 13, 2006 - 3:56 pm UTC
Dear Tom,
any help on this question (see previous post) will be very useful for us - we need to make some design decision based on the answer.
Thank you very much for your help!
January 15, 2006 - 3:19 pm UTC
documentation says they are not supported - therefore I would suggest not using them in a distributed transaction.
NET
Vitaliy, January 14, 2006 - 8:57 am UTC
I have the same problem as "A Reader" in a .NET Framework Application server, using distributed tranactions provided by COM+ component : Oracle.DataAccess.Client.OracleException ORA-14450 ...
I resolve this problem by changing from "CREATE GLOBAL TEMPORARY TABLE table1 (x int)" to "CREATE GLOBAL TEMPORARY TABLE table1 (x int) ON COMMIT PRESERVE ROWS". Also i include DELETE table1 steatment in the beginning of stored procedure. But i don't understand what was the problem is :(
January 15, 2006 - 3:40 pm UTC
the problem is - global temporary tables are documented to not be supported in distributed transactions.
Temp table not showing results after commit
Laks, February 01, 2006 - 12:28 pm UTC
Hi Tom,
In Oracle 9i, I have created a global temp table outside a procedure(on commit preserve rows).. Inside the procedure, i am inserting rows into it.. once it comes out of the procedure, i try to do a select on the temp table, it does not show any records.. I am using RapidSQL as my development tool and all this in one session. I have another tool 'Benthic'. When i do the same thing there and select from the temp table, it gives me all the records.. why is this happening..
Thanks in advance..
February 02, 2006 - 4:08 am UTC
you would have to ask the makers of rapidsql - a tool I don't have - why.
they must be doing something "funny" with the session - can you verify you have the same session in the plsql block as the select afterwards.
Global Temporary table
Mahesh, March 09, 2006 - 12:07 am UTC
CREATE GLOBAL TEMPORARY TABLE mygtt
( name VARCHAR2(10)
) ON COMMIT PRESERVE ROWS
/
select TABLESPACE_NAME,sum(BYTES) from dba_free_space where TABLESPACE_NAME='UNDOTBS1'
/
UNDOTBS1 1739718656
Populate mygtt with 12407296 records & commit;
select TABLESPACE_NAME,sum(BYTES) from dba_free_space where TABLESPACE_NAME='UNDOTBS1'
/
UNDOTBS1 1700921344
Is this means GTT uses UNDO/rollback segment to store data?
March 09, 2006 - 1:04 pm UTC
undo is definitely generated (think about it - we have read consistency on global temporary tables (GTT). we have the ability to rollback, we have the ability for statements to fail "gracefully" using undo to recover from the error).
and redo is always generated for UNDO.
GTT / Regular Table ???
Dhimant Antani, March 13, 2006 - 6:46 am UTC
hi tom,
I need a small clarification. One of my app's stored procedure creates dynamic tables on the fly..populates it and does a couple of selects on it. This is a reporting database,,, on generating an explain plan I found that these tables were undergoing FTS and the same was expensive in terms of cost. My Question is what would be good in terms of performance for a heavy reporting database?
1. Regular Tables?
2. Dynamic Tables created by the stored procedure and dropped after execution of the stored procedure?
3. Global Temporary Tables?
The version is
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
Thanks for all the info.
March 13, 2006 - 10:25 am UTC
why do you even believe you need these tables - we are not sqlserver after all. The use of temporary tables is generally not needed.
I would be looking to not use temporary scratch tables of any sort as they all imply additional processing (IO, undo, redo, everything)
Difference in Delete vs Truncate in Global temporary tables
koshal, June 03, 2006 - 10:56 am UTC
10:50:53 patibank@test>
10:50:53 patibank@test> drop table ISSUE_ID_TMP;
Table dropped.
Elapsed: 00:00:00.01
10:50:54 patibank@test> CREATE GLOBAL TEMPORARY TABLE ISSUE_ID_TMP (ISSUE_ID NUMBER) ON COMMIT PRESERVE ROWS;
Table created.
Elapsed: 00:00:00.00
10:50:54 patibank@test> INSERT INTO ISSUE_ID_TMP VALUES(1);
1 row created.
Elapsed: 00:00:00.01
10:50:54 patibank@test> declare
10:50:54 2 pragma autonomous_transaction;
10:50:54 3 begin
10:50:54 4 execute immediate 'TRUNCATE TABLE ISSUE_ID_TMP';
10:50:54 5 end;
10:50:54 6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
10:50:54 patibank@test> SELECT * FROM ISSUE_ID_TMP;
no rows selected
Elapsed: 00:00:00.00
10:50:54 patibank@test> /
no rows selected
Elapsed: 00:00:00.00
10:50:54 patibank@test> drop table ISSUE_ID_TMP;
Table dropped.
Elapsed: 00:00:00.02
10:50:55 patibank@test> CREATE GLOBAL TEMPORARY TABLE ISSUE_ID_TMP (ISSUE_ID NUMBER) ON COMMIT PRESERVE ROWS;
Table created.
Elapsed: 00:00:00.01
10:50:55 patibank@test> INSERT INTO ISSUE_ID_TMP VALUES(1);
1 row created.
Elapsed: 00:00:00.02
10:50:55 patibank@test> declare
10:50:55 2 pragma autonomous_transaction;
10:50:55 3 begin
10:50:55 4 execute immediate 'DELETE FROM ISSUE_ID_TMP';
10:50:55 5 COMMIT;
10:50:55 6 end;
10:50:55 7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
10:50:55 patibank@test> SELECT * FROM ISSUE_ID_TMP;
ISSUE_ID
----------
1
Elapsed: 00:00:00.02
10:50:56 patibank@test> /
ISSUE_ID
----------
1
Elapsed: 00:00:00.01
10:50:56 patibank@test>
10:50:56 patibank@test> spool off
Is it intended??
June 03, 2006 - 11:14 am UTC
It sort of makes sense - the delete in the autonomous transaction cannot see the inserted row (not until you commit).
The truncate just "wipes out the segment". The only "surprise" for me was that the truncate succeeded.
Surprised.
Koshal, June 04, 2006 - 11:16 am UTC
I am also surprised by the following.
1. In the case of normal (heap) table it is generating error why not in the case of global temporary table?
(Truncate of the global temporary table wipes only the current sessions data).
2. How are the statements in Autonomous transactions implemented. (if it is in the same session why is not able to read active/uncommited transactions. It is for sure not being implemented in different session. Because it is truncate the global temporary table.) Is it different for DDL/DML??
3. Do you see any downside/difficulities by Oralce in providing the ability/flexibility for autonomous transactions to read the transactions of the current session (session invoked) irrespective of whether it is commited or rolledback or atleast error out if it find some uncommitted transaction from the session it was invoked. This makes oracle consistent (Delete Vs Truncate)
I am finding it difficult to digest the fact oralce is not able to read active/uncommited transaction from the current session :).
Thanks
Koshal
June 04, 2006 - 4:18 pm UTC
1) because it is not a normal table, it is "different", it is a temporary table - not a permanent one. I would not expect things to be the same.
2) don't know what you mean there. It is the same session but a DIFFERENT transaction. consistency is a transaction thing, not a session thing.
3) see #2, consistency is not a "session" thing, it is entirely a "transaction thing". Start a new transaction and you cannot see uncommitted changes in other transactions - forget about sessions all together, they do not count here.
Thanks a lot.
Koshal, June 05, 2006 - 12:34 pm UTC
Thanks a lot Tom!
Autonomous temporary transaction table...
Asher, July 18, 2006 - 6:38 pm UTC
"ops$tkyte@ORA9IR2> create global temporary table t ( x int ) on commit delete
rows;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1);
1 row created.
ops$tkyte@ORA9IR2> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values ( 1 );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at line 4"
Okay... I have that precise problem. My question is... are you aware of a work-around other then clearing the temporary table calling the procedure?
I've implimented an package with similar functionality to how contexts work in oracle. Except in this case anybody/thing can read/write a variable and variables can store seperate string, numeric, and date datatypes. Behind the scenes these values are stored in a global temporary table. The issue comes in the fact that I have a view that calls a function which calls another view. Both views use this package to set view parameters effectively, but the function isn't able to set the values for the second view because the gtt isn't empty.
It would seem like an autonomous transaction, being a seperate transaction, should have it's own copy of a transactional temporary table? If there's no current workaround, I don't suppose you've heard anything of them potentially fixing this short-comming of the gtts?
July 19, 2006 - 9:03 am UTC
"why", what is the logic here that you need an autonomous transaction.
they are evil
they are rarely ever used correctly.
In my experience they almost always indicate "a serious logic flaw".
so, why are you forced to use them? what is the design aspect that makes them desirable?
Autonomous temporary transaction table...
Asher, July 20, 2006 - 1:53 pm UTC
Function A has the task of gathering the percentage of the way through an item on a training plan.
Function A is thus called from a view.
In order to call another view that function A needs it must set certain values that the view needs.
Behind the scenes, those values are stored in a global temporary table.
You can not alter (insert, update, delete) from a table within a select statement, or a function used in a select statement, unless that function has pragma autonomous transaction.
So function A is set as pragma autonomous action so it can set it's variables (which are stored in a temporary table) run the view, and then clear the temporary table... but as stated above it can't setup those values without being an autonomous transaction.
July 22, 2006 - 5:02 pm UTC
sounds like you need more than one GTT - for logical reasons.
You are using it alternatively for "inputs" and for "outputs".
Function A needs it's own object.
I don't know all of the details here - but sounds way overly complex. A Function called from a View that calls another view that needs a set of inputs. Lots of "magic" happening - rather that autonomous transactions, functions called from views that call other views etc etc etc - I'd probably be myself looking for something a little more "linear" and "straight forward"
ORA 14450 and connection pooling
Nagesh, January 29, 2007 - 1:22 pm UTC
I had the same issue recently using connection pool.
I have a stored procedure which does all the DB work and is called by an EJB, which gets a connection from pool and returns it when the DB call is done.
I noticed that the same connection ( and in turn same session ) is used by another thread even when the first transaction is pending and thus causing ORA 14450. Apparently J2EE containers open concurrent transactions in a single session and I am not sure how. Commit or rollback of one transaction does not affect the integrity of other. Transactions behave as if they are from two different sessions except for session variables and GTT.
The connection pool has a setting and by checking that we could make that a connection is dedicated to a single weblogic transaction. When I checked that option the connection is not reused until a commit or rollback issued, which solved the issue.
This behavior makes me to think that Oracle supports parallel transactions in a single session. Am I correct?
January 31, 2007 - 1:25 pm UTC
we have a feature called autonomous transactions - but that isn't what came into play here.
this sounds more like an XA related issue - you are using an external resource manager (your "containers") and it is controlling the transactions in all likelihood
ORA 14450
Nagesh, February 02, 2007 - 9:53 am UTC
Yes. You are correct about how container manages the transactions and hence opens concurrent sessions ( not autonomous ). They do this in order to allow more users to modify the data with a few connections/sessions. Concurrent transactions affect session variables and thus may persist incorrect data. Why should Oracle allows opening concurrent transactions when we have objects/variables which are session persistent and transactions blissfully unaware their state.
If this is a necessary evil then PL/SQL developers need to be careful about how their packages are used.
ORA 14450
Nagesh, February 02, 2007 - 9:59 am UTC
I meant concurrent transaction in a single sesion ( Not concurrent sesions )
Temporary tables
Rajesh, February 20, 2007 - 4:34 am UTC
Hi Tom,
Please give some inputs on the fallowing questios.
Questions:
a. Would a session level data (no commit to tables after processing) e.g.
('PS_ALLOC_P_SEL','PS_ALLOC_P_SEL001','PS_ALLOC_P_SEL002','PS_ALLOC_P_SEL003','PS_ALLOC_P_SEL004') processed at session level take space on or use the undo/redo?
Background: These are the "temp" tables used by GL alloc to process data but data is never committed to them. Stats are trick in these tables at 2000 rows each.
b. What are the benefits of using "temp" tables vs permanent tables (tables where you commit data to it)?
Thank you
February 20, 2007 - 9:46 am UTC
a) undo is always generated until you use insert /*+ append */, redo is always generated for undo.
Hence, regardless of table type - "permanent or temporary" - there will always be undo and redo generated unless you use a direct path load.
In this case, these are permanent tables really - they are not true temporary. Yes, undo and redo is fully generated.
b) temporary tables may generate less redo then permanent tables but their biggest bang for the buck is that they are self "cleaning" (they truncate themselves, no big delete) and they are very "multi-user" (no cross session unique constraint violations and such - no blocking/locking across session issues at all)
WHERE clause doesn't work
Mei Shukuo, March 07, 2008 - 12:09 pm UTC
Hi Tom,
Thank you for your great contribution to the database community. I have a question and hope I can receive your answer.
When I try to create a global temporary table with the following command:
CREATE GLOBAL TEMPORARY TABLE MY_TEMP
AS SELECT * FROM MY_TABLE WHERE ID=2;
The temp table was created OK but no data was populated. The "MY_TABLE" table only has three rows and two simple columns: ID and name. Is this a normal behavior? I tried it on other real tables and got the same results.
Thank you in advance for your response.
Mei
March 10, 2008 - 10:38 am UTC
sure it is normal.
you created a transactional temporary table - "on commit delete rows" is the default
and what is the last thing DDL does......
it commits
Ora-14450
Deba, March 25, 2008 - 11:34 am UTC
Hi,
My database version is 10.2.0.3. I am getting error Ora-14450 when I am trying to insert into temp table from the application. But I am not able to simulate this manually in sqlplus. Could you please tell me how I am facing this problem at the time of inserting into temp table ?
Thanks
Deba
March 26, 2008 - 8:28 am UTC
ops$tkyte%ORA10GR2> create global temporary table t ( x int ) on commit delete rows;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values ( 1 );
5 end;
6 /
declare
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at line 4
would need to know more about how the application does their transaction handling, but easy to replicate, you just need more than one transaction.
Help resolved production release issue
Alex, September 06, 2008 - 8:23 am UTC
I ran in to problems during a production release on one of our main banking systems this weekend, which were related to this. This post helped be resolve the situation so thanks for your help.
Reg Temp table
mahesh, April 06, 2010 - 10:25 pm UTC
Hi,
I have used global temp table on commit preserve rows.
During initialization of the screen i'm creating temp tables and all the manipulation done on screen has been stored on temp table ,
at the end of all the insert and delete operation i'm retrieving it from temp table and displaying it in the screen,at some point of time the temp table value is lost.So nothing is been displayed in screen.
I tried to find out the problem according to me the session got expires coz of that the value in the temp table becomes empty and last value inserted is stored.
can anyone help me out to solve this problem it will be great to get a help.
April 12, 2010 - 7:42 pm UTC
sounds like you are building a web based application and you assume that you get a connection all to yourself.
but you don't, you grab a connection from a connection pool and you give it back - when you grab a connection again, it is usually a DIFFERENT connection.
But, I'm guessing, because you really don't tell us very much here..
GTT is blocking sessions
A reader, April 16, 2010 - 1:17 am UTC
Hi,
We have a procedure that connects to multiple servers via dblink, retrieves data and then insert the data in a local GTT.
The procedure is called every 15 minutes by a cron job so every call, I assume, starts a new session with its own private GTT.
We've had multiple blocking sessions lately that are decreasing DB performance. I've noticed this happens when let's say server D is down and the procedure kept trying to connect to server D and don't continue trying to connect and get data from server E.
After 15 minutes another session is created (Session 2)when firing the same procedure and then the session kept on waiting trying to insert data for server D then we get "ORA-04021: timeout occurred while waiting to lock object" and session 2 continue with the next server E.
The GTT is on commit preserve rows and the lock types are CU and TO. What are these type of locks?
Question:
Is it not supposed that the data for a GTT is private for the session and that no other session can attempt to read the same temp segment? how come then there's bloking sessions? I appreciate your help.
GTT
------
CREATE GLOBAL TEMPORARY TABLE GTT ( "server" varchar(20), "STATUS" VARCHAR2(30 BYTE) ) ON COMMIT preserver ROWS ;
procedure
----------
PROCEDURE insert_GTT AS
cursor c is
SELECT dblink_name, servername SAASC.REGION, 'NO
FROM
server_table
v_dblink varchar(100);
v_servername varchar(20);
v_sql varchar(300);
begin
execute immediate ('truncate table T');
open c;
loop
begin
fetch c into v_dblink, v_servername
exit when c%notfound
v_sql : = ' insert into GTT (server, status) '||
' select '''||v_servername||''', status from ||
' table@'||v_dblink;
execute immediate (v_sql);
end;
end loop;
close c;
commit;
end insert_GTT;
V$lock
-------
000000057B0D88B8 000000057B0D88D8 1596 CU 1719743048 5 6 0 194567 0
000000057B0D9840 000000057B0D9860 1596 TO 311560 1 3 0 196097 0
Thanks a lot for your time.
April 16, 2010 - 9:46 am UTC
to - Temporary Table Object Enqueue
cu - bind enqueue
what is table T?
why are you truncating table T?
I think it has more to do with table T than anything else - and the truncate.
GTT is blocking sessions
A reader, April 16, 2010 - 3:13 pm UTC
sorry there was a typo. T is the global temporary table GTT. below is the corrected procedure.
-Does TO lock the temp table to avoid any changes to its ddl?
-What does CU lock prevents?
-Yesterday, we killed all sessions that have a lock to the GTT table. After checking v$session their status was Killed but their state was Waiting and after 21 hours it keeps the same. As consequence, they're still keeping the lock on the GTT table both the TO and the CU. What do we need to do to kill these sessions?
-Why is the blocking? I'm assuming a session locks a space in their own temp segment so no other session will have to wait for that space. Please correct me.
procedure
----------
PROCEDURE insert_GTT AS
cursor c is
SELECT dblink_name, servername
FROM
server_table
v_dblink varchar(100);
v_servername varchar(20);
v_sql varchar(300);
begin
execute immediate ('truncate table GTT');
open c;
loop
begin
fetch c into v_dblink, v_servername
exit when c%notfound
v_sql : = ' insert into GTT (server, status) '||
' select '''||v_servername||''', status from ||
' table@'||v_dblink;
execute immediate (v_sql);
end;
end loop;
close c;
commit;
end insert_GTT;
Thanks again for yor help and time.
how to achive
Abhisek, August 16, 2010 - 9:36 am UTC
Hi tom,
How can I create the table through execute immediate script and use in the same transaction..
DECLARE
a number;
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY DROP TABLE t ON COMMIT preserve ROWS AS SELECT * FROM demo';
SELECT t.AMT into A
FROM t, demo
where t.AMT=10
and demo.amt=t.amt;
DBMS_OUTPUT.PUT_LINE(a);
END;
August 19, 2010 - 1:12 am UTC
why - that would be "not smart"
we run ddl WAY AHEAD OF TIME and don't touch it (this is NOT sqlserver, we are different - you will need slightly different processing for each and every database you use)
You will not do this - it just isn't the right approach - no matter what you've done in other databases, no matter what anyone says - it is wrong.
Followup
Abhisek, August 19, 2010 - 3:26 pm UTC
Thanks Tom,
I know you were busy so decided to make a global temporary table before I use them. Now I insert the row in the global temporary table.
Thanks for your reply.
Is temp table in memory
Abhisek, August 20, 2010 - 6:12 pm UTC
Hi Tom,
As I said earlier I have created the temporary table seperately and from the package I am inserting data into it with append mode.
When package is next run, it truncates the table.
My question is: If the packge is running, is it that the temporary table with the data, remains in the memory? I mean if the data remains in memory and the disk is not accessed as in the normal scenario.
I am sorry for a simple question for you, but I didnt have the knowlwdge about global temporary table.
August 25, 2010 - 12:51 pm UTC
temporary tables are cached and buffered just like regular tables - their data might be on disk, might be in memory - you and I have no control over it.
Meaning "it depends", the answer to your question is alternately "yes" and "no" depending on the circumstances at runtime. Given the same test case - one day it might be "all about memory" and the next day "all about disk"
GTT and distributed transactions
Jayadevan, July 13, 2011 - 12:10 am UTC
Hi Tom,
We have been using global temporary tables in distributed transactions for a long time, since we use two transaction managers - jdbc and jms. The target db/instance is the same.We kept running into temporary table already in use error once in a while (well, very rarely). We are not doing any DDL, mainly inserts/updates. Recently, the frequency of errors went up (may be because the load itsefl has been going up stradily) and searching in metalink we realized that GTTs are not supported in distributed transactions (Document id 414168.1 in metalink). How can we replace GTT with another approach? We are considering using actual tables to replace GTTs. The actual tables will have one extra column to store session id. This means quite a lot of code change and we have concerns about the performance impact also. Are there other approaches we could consider? Please share your thoughts.
Thanks.
July 13, 2011 - 8:08 pm UTC
global temporary tables are *not* supported in distributed transactions - that it worked sometimes would be a bug if there is a bug here at all.
I'd need to understand what and why you are using the gtt's for - without understanding the logic - I cannot really suggest a better way.
ora-14450 error accessing table
Sathish, December 27, 2011 - 8:33 am UTC
I had the GTT access issue and got resolved by removing the dblink and created synonyms instead. In a PLSQL block if there is a dblink and GTT involved then this issue would be there. The error happened only at the customer site and has been resolved by doing the above changes. I could not simulate in my environment.