{"id":4802,"date":"2025-09-02T05:40:30","date_gmt":"2025-09-02T05:40:30","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4802"},"modified":"2025-09-02T05:40:31","modified_gmt":"2025-09-02T05:40:31","slug":"oracle-kill-session-alter-system-kill-session","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-kill-session-alter-system-kill-session\/","title":{"rendered":"Oracle Kill Session \u2013 ALTER SYSTEM KILL Session"},"content":{"rendered":"\n

Database administrators often face sessions that hang, lock resources, or consume unnecessary memory. Oracle provides multiple ways to terminate or disconnect sessions, depending on the scenario. In this guide, we\u2019ll cover ALTER SYSTEM KILL SESSION<\/code><\/strong> and ALTER SYSTEM DISCONNECT SESSION<\/code><\/strong>, explain the syntax in detail, and provide examples for both single-instance and RAC (Real Application Clusters) environments.<\/p>\n\n\n\n

Understanding the Syntax<\/h2>\n\n\n\n

Oracle uses the following syntax to end sessions:<\/p>\n\n\n\n

ALTER SYSTEM { \n    DISCONNECT SESSION 'integer1, integer2' [ POST_TRANSACTION ] \n    | KILL SESSION 'integer1, integer2 [,@integer3]' \n} [ IMMEDIATE | NOREPLAY ];<\/code><\/pre>\n\n\n\n
Explanation of Each Component<\/h3>\n\n\n\n
Component<\/th>Description<\/th><\/tr><\/thead>
ALTER SYSTEM<\/code><\/td>A system-level command used to modify database behavior, here for terminating or disconnecting sessions.<\/td><\/tr>
KILL SESSION 'SID, SERIAL# [, @INST_ID]'<\/code><\/td>Marks a session for termination, rolls back uncommitted transactions, and releases locks.<\/td><\/tr>
DISCONNECT SESSION 'SID, SERIAL#'<\/code><\/td>Disconnects a session by killing the server process or virtual circuit.<\/td><\/tr>
POST_TRANSACTION<\/code><\/td>Optional for DISCONNECT; disconnects after the current transaction completes (graceful).<\/td><\/tr>
IMMEDIATE<\/code><\/td>Forces rollback and resource release instantly.<\/td><\/tr>
NOREPLAY<\/code><\/td>Prevents session recovery under Application Continuity (AC).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Placeholders Explained<\/h3>\n\n\n\n
Placeholder<\/th>Meaning<\/th><\/tr><\/thead>
SID<\/code> (integer1<\/code>)<\/td>Session ID, unique per session in the instance.<\/td><\/tr>
SERIAL#<\/code> (integer2<\/code>)<\/td>Serial number of the session, used with SID to uniquely identify a session.<\/td><\/tr>
@INST_ID<\/code> (integer3<\/code>)<\/td>Optional instance ID for RAC; specifies which instance the session belongs to.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Kill Session vs Disconnect Session<\/h2>\n\n\n\n
Command<\/th>Behavior<\/th><\/tr><\/thead>
KILL SESSION<\/code><\/td>Marks session for termination, waits for ongoing transactions unless IMMEDIATE<\/code> is specified.<\/td><\/tr>
DISCONNECT SESSION<\/code><\/td>Terminates the server process or virtual circuit; can be immediate or post-transaction.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n
  • KILL SESSION<\/strong> \u2192 Less aggressive, preferred for normal terminations.<\/li>\n\n\n\n
  • DISCONNECT SESSION<\/strong> \u2192 More forceful, use carefully.<\/li>\n<\/ul>\n\n\n\n
    Practical Examples with Randomized Placeholders<\/h2>\n\n\n\n
    1. Normally Kill a Session in the Current Instance<\/h3>\n\n\n\n
    Retrieve session identifiers from V$SESSION<\/code>:<\/p>\n\n\n\n
    SELECT sid, serial# \nFROM v$session \nWHERE username = 'HR_APP01' \n  AND lower(machine) = lower('LAPTOP-XY12AB') \n  AND lower(program) LIKE lower('%sqlplus%');<\/code><\/pre>\n\n\n\n
    Example output:<\/p>\n\n\n\n
    SID<\/th>SERIAL#<\/th><\/tr><\/thead>
    301<\/td>45211<\/td><\/tr>
    402<\/td>87452<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Kill sessions:<\/p>\n\n\n\n
    ALTER SYSTEM KILL SESSION '301, 45211';\nALTER SYSTEM KILL SESSION '402, 87452';<\/code><\/pre>\n\n\n\n
    2. Kill a Session in RAC (Specify Instance)<\/h3>\n\n\n\n
    Use GV$SESSION<\/code> for RAC:<\/p>\n\n\n\n
    SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ', ' || serial# || ', @' || inst_id || ''';' AS stmt\nFROM gv$session\nWHERE username = 'HR_APP01'\n  AND lower(machine) = lower('LAPTOP-XY12AB')\n  AND lower(program) LIKE lower('%sqlplus%');<\/code><\/pre>\n\n\n\n
    Example output:<\/p>\n\n\n\n
    ALTER SYSTEM KILL SESSION '1203, 3345, @2';\nALTER SYSTEM KILL SESSION '1804, 7721, @3';<\/code><\/pre>\n\n\n\n
    3. Immediately Kill a Session<\/h3>\n\n\n\n
      \n
    • Current instance:<\/li>\n<\/ul>\n\n\n\n
      ALTER SYSTEM KILL SESSION '301, 45211' IMMEDIATE;<\/code><\/pre>\n\n\n\n
        \n
      • RAC instance:<\/li>\n<\/ul>\n\n\n\n
        ALTER SYSTEM KILL SESSION '1203, 3345, @2' IMMEDIATE;<\/code><\/pre>\n\n\n\n
        4. Kill Session Without Application Continuity Recovery<\/h3>\n\n\n\n
          \n
        • Current instance:<\/li>\n<\/ul>\n\n\n\n
          ALTER SYSTEM KILL SESSION '301, 45211' NOREPLAY;\n<\/code><\/pre>\n\n\n\n
            \n
          • RAC instance:<\/li>\n<\/ul>\n\n\n\n
            ALTER SYSTEM KILL SESSION '1203, 3345, @2' NOREPLAY;<\/code><\/pre>\n\n\n\n
            5. Disconnect a Session<\/h3>\n\n\n\n
              \n
            • Normally (after transaction completes):<\/li>\n<\/ul>\n\n\n\n
              ALTER SYSTEM DISCONNECT SESSION '301, 45211' POST_TRANSACTION;<\/code><\/pre>\n\n\n\n
                \n
              • Immediately (forceful termination):<\/li>\n<\/ul>\n\n\n\n
                ALTER SYSTEM DISCONNECT SESSION '301, 45211' IMMEDIATE;<\/code><\/pre>\n\n\n\n
                \n
                Important:<\/strong> Always specify POST_TRANSACTION<\/code> or IMMEDIATE<\/code> with DISCONNECT SESSION<\/code>, otherwise the command fails.<\/p>\n<\/blockquote>\n\n\n\n
                Quick Reference Table<\/h2>\n\n\n\n
                Scenario<\/th>Command Example<\/th><\/tr><\/thead>
                Normal kill (current instance)<\/td>ALTER SYSTEM KILL SESSION 'SID, SERIAL#';<\/code><\/td><\/tr>
                Normal kill (RAC instance)<\/td>ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID';<\/code><\/td><\/tr>
                Immediate kill (current instance)<\/td>ALTER SYSTEM KILL SESSION 'SID, SERIAL#' IMMEDIATE;<\/code><\/td><\/tr>
                Immediate kill (RAC instance)<\/td>ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID' IMMEDIATE;<\/code><\/td><\/tr>
                Kill without AC recovery<\/td>ALTER SYSTEM KILL SESSION 'SID, SERIAL#' NOREPLAY;<\/code><\/td><\/tr>
                Normal disconnect<\/td>ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' POST_TRANSACTION;<\/code><\/td><\/tr>
                Immediate disconnect<\/td>ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' IMMEDIATE;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                Conclusion<\/h2>\n\n\n\n
                Oracle provides flexible ways to manage sessions in both single-instance and RAC environments. By understanding the syntax, placeholders (SID<\/code>, SERIAL#<\/code>, INST_ID<\/code>), and the different scenarios, DBAs can safely terminate or disconnect sessions without impacting database stability. This guide ensures you have all scenarios covered<\/strong>, from normal termination to immediate disconnection, with RAC support and Application Continuity considerations.<\/p>\n","protected":false},"excerpt":{"rendered":"
                Database administrators often face sessions that hang, lock resources, or consume unnecessary memory. Oracle provides multiple ways to terminate or disconnect sessions, depending on the scenario. In this guide, we\u2019ll cover ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION, explain the syntax in detail, and provide examples for both single-instance and RAC (Real Application […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4802","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}