I want alter a temporary table, adding some columns. The alter table statement throws ORA-14450 attempt to access a transactional temp table already in use.
We have a fairly large test environment, running many users and application. My applications using the table are an asp.net application and a pl/sql monitoring application. Restarting webserver and database is not possible to find remove session.
Any idea to find session blocking my alter table?
I did some research too.
Oracle uses a lock mode TO (maybe transactional object?). If you issue the following query, you get the session holding the lock.
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 = '<your object name>'
AND a.id1 = o.object_id
AND a.type = 'TO'
AND a.sid = s.sid
/
I found nothing about this in oracle documentation. But killing the named session removes the look. Maybe there is a more graceful method of abandoning the transaction but I didn't found any other.
I found this:
Perhaps it will help?
Update:
Maybe the thing to do is search for any lock on that table before you use it with a query like:
select
s.username,
s.ouser,
s.machine,
s.sid
from user_objects o
inner join v$lock v
on o.object_id = v.id1
inner join v$session
on v.sid = s.sid
/* maybe add where s.sid <> my_sid but I don't know how to find my sid */
and throw an exception or log the output from that command if any rows are returned? If you're doing this in a procedure, I noticed that you can conditionally execute debug statements starting somewhere around version 9i.
