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!
Original comment by Jeroen:
Leuk in combinatie hiermee: client identifier
CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’)
||’:’||
SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’)
INTO v_user_identifier
FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
daarna s.client_identifier toevoegen en hopla
Original comment by Bas:
The above won’t work if the session has status=killed. In that case retrieve the system process id with:
select s.username,s.osuser,s.sid,s.serial#,p.spid
from v$session s,v$process p
where s.paddr = p.addr
and s.username is not null;
and use kill -9 on unix to kill the session.