Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 18 July 2012

How to kill the User Session on Windows/Linux

Generally needness of killing user session arises in a situation if the user session is hang or you are feeling that user performing suspicious activity or in a circumstances when you want stop all other users for any particular maintenance work and he is not available to log out. While killing the user session you must careful about the sessions, processes and SQL associated with that session.
--Gathering information before killing session
rpad(||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from v$sysstat a, v$license b, v$database c
where = 'logons cumulative' ;
--Displaying sid and and serial# (required for killing) along with other information
substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box, substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user, substr(b.program,1,30) program
from v$session b, v$process a
where b.paddr = a.addr and type='USER'
order by spid;
--displaying message, what to do, to kill the session
''''||'SID, SER#'||''''||';' from dual;
From the above query you can separate the sid and serial# essential for killing the session
Note: Use restricted session command to stop new sessions from connecting during this period ALTER SYSTEM ENABLE RESTRICTED SESSION. If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode: ALTER SYSTEM QUIESCE RESTRICTED;
Then kill the session those who are already active. Now in restricted mode, no user can logged on accept for DBA user.
For better approach you can generate all the session to be killed with sid and serial# as:
select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session;
In some situations the oracle.exe is not able to kill the session immediately. In these cases the session will be "marked for kill". It will then be killed as soon as possible.
It is possible to force the kill by adding the IMMEDIATE keyword:
Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an oracle session. If the marked session persists for some time you may consider killing the process at the operating system level. However, this is not recommended as it is dangerous and can lead to instance failures.
For Example: To kill the block Session:
Alter system kill session '16,67282';
For killing sessions in UNIX you have to locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.
% kill -9 spid
Incase of doubt you can check that the SPID matches the UNIX Process ID by using below command.
% ps -ef | grep ora
Killing the session via OS level:
SELECT s.sid, p.spid, s.osuser, s.program
FROM v$process p,  v$session s
WHERE p.addr = s.paddr;
The SID and SPID values of the relevant session can then be substituted in the below on windows platform as:
C:\> orakill ORACLE_SID spid
The Windows command to kill this session will be as under
C:\oracle9i\bin>orakill orcl3 9276
In the above example, the windows thread corresponding to the Oracle session can be killed in the operating system without ever logging into the database.
For Example:
Select a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr = b.addr and a.username is not null;
USERNAME                      OSUSER      SPID
-------------------           ---------   -----
HR                            HR          2018
HRMS                          shahid      3361
Scott                         scott       1768
Shahid1                       shahid      9276
Now after running the above orakill you will notice the user shahid1 is gone.
Note: You can also obtain the Oracle SPID by using tool like QuickSlice from Microsoft
So the important question is why oracle provides a utility to kill the session from OS level as the DBA is able to kill the user session within the database level.
There are some situation or reason a DBA might use orakill instead of alter system kill session command.
– The alter system will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.
– A DBA is able to kill the session without ever logging in to the database.


Post a Comment