Quote Originally Posted by Tuma View Post
I have some temp tables. Somtimes I need to modify it to add a column.
But usually this table has a session open on it and would give below error. I resolve it by just killing sessions. But I would like to find just that session or sessions that are the culprits rather than blindly killing all the sessions.

-Question - How i do find the session/s that has the temp table open now?

Thanks

ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
This one may come handy...
Code:
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
;