Does Oracle complain about not being able to drop a table for a currently connected user, but you are sure you disconnected? Do the sessions “hang” in “inactive” state? Just log on as system, and execute the following query:
SELECT
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || '''; --',
s.sid,
s.serial#,
s.osuser,
s.username,
s.program,
status
FROM v$session s
WHERE status = 'INACTIVE';
You will get a list of statements you need to execute (just copy-pase) to kill the inactive sessions. Don’t listen to the guys telling you to do intricate System Administrator stuff on a command prompt, just use any JDBC tool.
EDIT: Single query which also lists the kill command if the “alter system kill session” trick did not work:
SELECT
'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# ||
'''; -- kill -9 ' || p.spid,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and (s.state='INACTIVE' or s.state='KILLED');
Thanks Bas en Jeroen!
