Oracle -- How to Kill Sessions for Replicate

[David_Fergen]

Description:

This guide explains how to kill Oracle sessions.

Resolution:

To kill the connection from the client side, use TCPVIEW. 

Link for TCPVIEW:

https://technet.microsoft.com/en-us/sysinternals/tcpview.aspx 

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows:

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

  

Link that properly explains how to kill Oracle:

<https://oracle-base.com/articles/misc/killing-oracle-sessions>  

  

The basic syntax for killing a session is shown below. 

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

In a RAC environment, make sure to optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node. 

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

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible. 

In addition to the syntax described above, also add the IMMEDIATE clause. 

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