Search This Blog

Thursday, March 8, 2012

How to kill sessions in oracle database.


Execute the following script to list all sessions running in your database.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

For RAC environment.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

The above command merely asks the session running to kill itself.
In some cases the session will not get killed immediately beacuse
of some ongoing operations, beacause of that that session
will have a status of "MARKED FOR KILL". It will be killed as soon as the
operation is completed.

The UNIX Approach

% kill spid

If after few minutes the process does not get killed, execute the following command.

% kill -9 spid

check for running oracle processes.

% ps -ef | grep ora

0 comments:

Post a Comment