Rolfje's blog

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!

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

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 <psid> on unix to kill the session.

tory burch outlet

http://www.cheap-oakley-sunglasses.us
http://www.birkenstockoutletus.net
http://www.toryburchoutletsaless.net
http://coach-online-outlet.com