Sometimes you see sessions does not wipe out immediately or you see “marked for kill” message and they stays in killed state. You can kill them using below query.
declare
CURSOR KILL_S IS select SID,S.SERIAL# SERIAL from v$session S where STATUS not in ('ACTIVE','INACTIVE');
STR2 VARCHAR2(500);
BEGIN
FOR I IN KILL_S
LOOP
STR2:='alter system disconnect session ''' || I.SID || ',' || I.SERIAL || ''' immediate';
dbms_output.put_line(STR2);
EXECUTE IMMEDIATE STR2;
END LOOP;
END;
/
I can’t. Report ORA-00026：missing or invalid session ID..
must be run ” kill -9 spid” in os.
select ‘kill -9 ‘||spid from v$process where addr in(select creator_addr from v$session where status=’KILLED’);
Yes, if session has valid addr, the script will work fine.