📋Steps in this guide1/6

Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT SESSION)

Kill Oracle sessions safely using the ALTER SYSTEM KILL / DISCONNECT SESSION command, or directly from Windows or UNIX/Linux.

oracle miscconfigurationintermediate

by OracleDba

105 views

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below. In a RAC environment, you optionally specify the , shown when querying the view. This allows you to kill a session on different RAC node. The 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, you can add the clause. This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script ( session_undo.sql ). If the value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

Code/Command (click line numbers to comment):

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

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

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

ALTER SYSTEM DISCONNECT SESSION

The syntax is an alternative method for killing Oracle sessions. Unlike the command which asks the session to kill itself, the command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the command with the addition of the clause. The and values of the relevant session can be substituted into one of the following statements. The clause waits for ongoing transactions to complete before disconnecting the session, while the clause disconnects the session and ongoing transactions are rolled back immediately. The and clauses can be used together, but the documentation states that in this case the clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error. This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

Code/Command (click line numbers to comment):

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

SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
                                     *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>

ALTER SYSTEM CANCEL SQL (18c+)

The command was introduced in Oracle Database 18c to cancel a SQL statement in a session, providing an alternative to killing a rogue session. This functionality is discussed in the following article.

Code/Command (click line numbers to comment):

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant and values into the following command issued from the command line. The session thread should be killed immediately and all resources released.

Code/Command (click line numbers to comment):

C:\> orakill ORACLE_SID spid

The UNIX Approach

Warning : If you are using the Multithreaded Model in Oracle 12c, you should not attempt to kill operating system processes. To know why, read this . To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant into the following command. If after a few minutes the process hasn't stopped, terminate the session using the following. If in doubt check that the matches the UNIX shown using. The session thread should be killed immediately and all resources released.

Code/Command (click line numbers to comment):

% kill spid

% kill -9 spid

% ps -ef | grep ora

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash. Identify the offending session using the and views as follows. The and values of the relevant session can then be substituted into the commands in the previous sections. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       --s.sql_id,
       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';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       [email protected] (TNS V1-V3)
         1         23        287 3834       SYS        [email protected] (TNS V1-V3)
         1         40        387 4663                  [email protected] (J000)
         1         38        125 4665                  [email protected] (J001)

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!