Cleanup of Oracle killed session

Question

I killed a session

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

But it remains in v$session for 10 days, although an attached process is absent in v$processes.

SELECT
    s.username,
    s.osuser,
    s.sid,
    s.serial#,
    p.spid
  FROM v$session s, v$process p
 WHERE s.sid = '123'
   AND p.addr (+)= s.paddr;

I've also checked all Linux processes related to Oracle and found no zombies: all present processes are consistent to alive sessions.

This happens not for all the killed sessions. It seems that PMON is active, because the most of the killed sessions are cleaned from the list in a reasonable time (often it takes a second).

What can be done to eliminate long-playing killed session from the list?

Answer 1

I think this expected behavior for an inactive session that has been killed.

I have a session connected as a user 'JAY' which is in 'INACTIVE' state.

 [oracle@myserver datafile]$ sqlplus jay@hrpdb
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Let's check session details using sys user.

SQL> col username format a20

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  INACTIVE

Let's kill it.

SQL> alter system kill session '15,36102';

System altered.

Check session details once again.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  KILLED

It is still there in 'KILLED' state.

Now let's try to do some operation in Jay's session which was inactive and killed.

SQL> select * from tabs;
select * from tabs
*
ERROR at line 1:
ORA-00028: your session has been killed

Now let's check session details using SYS user.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

no rows selected

The session is no longer visible in v$session.

Answer 2

After you kill session you can use:

alter system disconnect session 'SID,SERIAL#' immediate;

To generate such commands use following script:

select 'alter system disconnect session ''' || SID || ',' || SERIAL# || ''' immediate;' from v$session where STATUS not in ('ACTIVE','INACTIVE');