Power By 专否社区

ORA-14450: attempt to access a transactional temp table already in use

Go To StackoverFlow.com

1

I got this error in my java application log, it uses java db connection pool. Anyone experienced this before using oracle temporary table? and what's your solution? Any help is much appreciated.

2012-04-04 16:51
by tpeng
A solution may depend on the way you use the temporary table and how you created it. Did you create it with ON COMMIT PRESERVE ROWS ? if so, do you really need to preserve the data for the whole session ? maybe you can work with ON COMMIT DELETE ROWS which holds the data for the transaction only ? tell us more .. - A.B.Cade 2012-04-04 17:34
The temporary table was created with 'ON COMMIT DELETE ROWS', and the pl/sql called by the java app issues a commit to end the transaction - tpeng 2012-04-04 23:32

2

Try to Terminate the connection and start back. To find whether any process is running try to use the below code and find the running process.
Error 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.

SELECT 
   o.object_name
 , s.sid, s.serial#
 , s.username
 , s.osuser, s.machine 
 , 'alter system kill session '''||to_char(s.sid)||','||to_char(s.serial#)||''';' ks  
FROM 
   user_objects o
 , v$lock a
 , v$session s  
WHERE 
 o.object_name = 'table_name_here'     
AND  a.id1 = o.object_id    
AND  a.type = 'TO'    
AND  a.sid = s.sid
;
2012-04-04 17:06
by SenthilPrabhu
Ads