1

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?

2

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.

  • Thank you, anyway your answer is very informative. But in this case we deal with a lost session which was not properly closed by client, so there's no way to operate with it. – diziaq Aug 21 '17 at 9:31
  • @diziaq not sure what you mean "there's no way to operate with it" ? The session can still be used by a client or impersonated as a user in Toad/SQL Developer to operate and validate this behavior. You should check the timeout settings (IDLE_TIME/EXPIRE_TIME) for this user/profile to confirm. – MguerraTorres Aug 21 '17 at 13:36
  • Maybe I was not precise, sorry for that. I've created a session in SQL Developer and after closing the application in couple of minutes I found that session (detected by OSUSER, MACHINE, PROGRAM columns in V$SESSION) remaining in WAITING state. So I killed it using standard command shown in the question. Now definitely that session in unavailable for me, this is what I meant by "no way to operate with it" – diziaq Aug 21 '17 at 14:20
  • Parallel discussion going on in: stackoverflow.com/questions/45730004/… – sandman Aug 22 '17 at 11:08
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.