When an inactive session is killed via “alter system kill session 'SID,SERIAL#'” command, the process continues in v$session with status as KILLED.
| SQL> Select username,sid,serial#,status from v$session where username = 'USERA'; USERNAME SID SERIAL# STATUS --------------- ---------- ---------- -------- USERA 147 720 INACTIVE |
| SQL> alter system kill session '147,720'; System altered. |
| SQL> Select USERNAME, SID, PROCESS, STATUS, PROGRAM from v$session where username = 'USERA'; USERNAME SID PROCESS STATUS PROGRAM --------------- ---------- ------------ -------- -------------------- USERA 147 4956:4848 KILLED sqlplus.exe |
| SQL> show user USER is "USERA" SQL> select * from user_tables; select * from user_tables * ERROR at line 1: ORA-00028: your session has been killed |
|
|
| SQL> Select USERNAME, SID, PROCESS, STATUS, PROGRAM from v$session where username = 'USERA'; no rows selected |
This happens when the client is SQL*Plus and Forms.
When the user tries to use the terminated session, the message ORA-00028 is returned.
When an inactive session has been terminated, STATUS in the view V$SESSION is "KILLED." The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.
If an active session cannot be interrupted (for example, it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated; if the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of "KILLED" and a server that is something other than "PSEUDO."
Please note that KILLing a background thread (DBWR, LGWR, SMON or PMON) will crash the instance.
