temp table query issue

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

Quote:

---

Originally Posted by Tuma

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
;

---

Thanks....i'll try that....