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
;