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;
/

About The Author

Comments

  1. zhwsh

    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’);

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.