ORA-00028 Your Session Has Been Killed - Session Termination Recovery

ORA-00028: Your Session Has Been Killed

Error Overview

Error Text: ORA-00028: your session has been killed

This error occurs when a database session has been forcibly terminated by a DBA using the ALTER SYSTEM KILL SESSION command or by Oracle’s internal processes due to various conditions. The session cannot continue processing and must reconnect.

Understanding Session Termination

Termination Methods

Session Termination Types
├── Manual Kill (ALTER SYSTEM KILL SESSION)
├── Automatic Kill (Resource limits, timeouts)
├── Instance Shutdown/Restart
├── Network Disconnection
└── Process Death (PMON cleanup)

Kill Session Modes

• IMMEDIATE - Forces immediate termination
• Default - Waits for current transaction to complete
• POST_TRANSACTION - Waits for current transaction, then kills

Common Causes

Administrative Actions

-- Manual session termination by DBA
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Resource limit exceeded
-- Profile-based limits triggered
-- Idle timeout reached

System-Related Causes

• Database shutdown/restart
• Instance failures or crashes
• Network connectivity issues
• Resource exhaustion (memory, processes)
• Deadlock resolution

Application Issues

• Long-running transactions blocking others
• Runaway queries consuming resources
• Connection pool issues
• Application hangs or freezes

Diagnostic Queries

Check Session Status

-- Verify if session still exists
SELECT
    sid,
    serial#,
    username,
    status,
    machine,
    program,
    logon_time,
    last_call_et
FROM v$session
WHERE sid = &session_id;  -- Replace with your session ID

Recent Session Kills

-- Check alert log for recent session kills (if available in v$diag_info)
SELECT
    timestamp,
    message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%kill%session%'
AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

Resource Limit Violations

-- Check for resource limit violations
SELECT
    username,
    resource_name,
    current_utilization,
    max_utilization,
    limit_value
FROM dba_users u,
     v$resource_limit r
WHERE u.profile = 'YOUR_PROFILE'  -- Replace with actual profile
AND resource_name IN (
    'IDLE_TIME',
    'CONNECT_TIME',
    'CPU_PER_SESSION',
    'LOGICAL_READS_PER_SESSION'
);

Profile Limits

-- Check profile resource limits
SELECT
    profile,
    resource_name,
    resource_type,
    limit
FROM dba_profiles
WHERE profile IN (
    SELECT DISTINCT profile
    FROM dba_users
    WHERE username = USER
)
AND resource_name IN (
    'IDLE_TIME',
    'CONNECT_TIME',
    'CPU_PER_SESSION',
    'SESSIONS_PER_USER'
)
ORDER BY profile, resource_name;

Resolution Steps

Immediate Recovery

1. Reconnect to Database

-- Simple reconnection
CONNECT username/password@database

-- Or from application
-- Re-establish database connection
-- Retry failed operations

2. Check Transaction Status

-- Verify if previous transactions were committed
SELECT
    table_name,
    num_rows,
    last_analyzed
FROM user_tables
WHERE table_name = 'YOUR_TABLE';  -- Check if data was committed

-- Check for any pending transactions
SELECT
    addr,
    xidusn,
    xidslot,
    xidsqn,
    status
FROM v$transaction;

Investigation Steps

1. Review Alert Log

# Check alert log for session kill messages
tail -f $ORACLE_BASE/diag/rdbms/*/alert_*.log | grep -i "kill"

# Look for patterns like:
# "Killing session sid: 123, serial: 456"
# "Session 123 killed by user request"

2. Check System Events

-- Look for system events that might cause kills
SELECT
    event_time,
    event_type,
    event_level,
    message_text
FROM v$diag_alert_ext
WHERE event_time > SYSDATE - 1
AND (message_text LIKE '%session%' OR message_text LIKE '%kill%')
ORDER BY event_time DESC;

3. Resource Usage Analysis

-- Check historical resource usage
SELECT
    begin_interval_time,
    stat_name,
    value
FROM dba_hist_sysstat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
AND stat_name IN (
    'session kills',
    'enqueue timeouts',
    'enqueue deadlocks'
)
AND begin_interval_time > SYSDATE - 7
ORDER BY begin_interval_time DESC;

Prevention Strategies

Application Design

1. Connection Management

// Proper connection handling with retry logic
public void executeWithRetry(String sql) {
    int maxRetries = 3;
    for (int i = 0; i < maxRetries; i++) {
        try (Connection conn = getConnection()) {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            conn.commit();
            break;  // Success, exit retry loop
        } catch (SQLException e) {
            if (e.getErrorCode() == 28) {  // ORA-00028
                if (i < maxRetries - 1) {
                    // Wait before retry
                    Thread.sleep(1000 * (i + 1));
                    continue;
                }
            }
            throw e;  // Re-throw if not retryable or max retries reached
        }
    }
}

2. Transaction Management

-- Use shorter transactions
BEGIN
    -- Keep transactions small and fast
    UPDATE table1 SET col1 = value1 WHERE id = 1;
    COMMIT;  -- Commit frequently

    UPDATE table2 SET col2 = value2 WHERE id = 1;
    COMMIT;
END;
/

Resource Limit Configuration

1. Profile Management

-- Create appropriate resource profiles
CREATE PROFILE app_user_profile LIMIT
    IDLE_TIME 60                    -- 60 minutes idle time
    CONNECT_TIME 480                -- 8 hours connect time
    CPU_PER_SESSION UNLIMITED
    LOGICAL_READS_PER_SESSION UNLIMITED
    SESSIONS_PER_USER 5;            -- Limit concurrent sessions

-- Assign profile to users
ALTER USER app_user PROFILE app_user_profile;

2. Session Monitoring

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_long_sessions AS
BEGIN
    FOR rec IN (
        SELECT sid, serial#, username, program, last_call_et
        FROM v$session
        WHERE status = 'ACTIVE'
        AND last_call_et > 3600  -- Running for more than 1 hour
        AND username IS NOT NULL
    ) LOOP
        -- Log long-running sessions
        INSERT INTO session_monitor_log VALUES (
            SYSDATE,
            rec.sid,
            rec.serial#,
            rec.username,
            rec.program,
            rec.last_call_et,
            'LONG_RUNNING'
        );
    END LOOP;
    COMMIT;
END;
/

Recovery Procedures

Application Recovery

# Python example with retry logic
import cx_Oracle
import time

def execute_with_recovery(connection, sql, params=None):
    max_attempts = 3
    attempt = 0

    while attempt < max_attempts:
        try:
            cursor = connection.cursor()
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            connection.commit()
            return cursor.fetchall()

        except cx_Oracle.DatabaseError as e:
            error_code = e.args[0].code
            if error_code == 28:  # ORA-00028
                attempt += 1
                if attempt < max_attempts:
                    time.sleep(2 ** attempt)  # Exponential backoff
                    # Reconnect
                    connection = cx_Oracle.connect(user, password, dsn)
                    continue
            raise e

Data Consistency Checks

-- Verify data consistency after session kill
SELECT
    table_name,
    COUNT(*) as row_count,
    MAX(last_modified) as last_update
FROM user_tables t,
     (SELECT table_name, MAX(timestamp) as last_modified
      FROM user_tab_modifications
      GROUP BY table_name) m
WHERE t.table_name = m.table_name(+)
GROUP BY table_name, last_modified
ORDER BY last_update DESC NULLS LAST;

Monitoring and Alerting

Session Kill Monitoring

-- Create view for session kill tracking
CREATE OR REPLACE VIEW session_kill_monitor AS
SELECT
    EXTRACT(HOUR FROM timestamp) as hour_of_day,
    COUNT(*) as kills_per_hour,
    AVG(COUNT(*)) OVER (ORDER BY EXTRACT(HOUR FROM timestamp)
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_kills
FROM v$diag_alert_ext
WHERE message_text LIKE '%session%kill%'
AND timestamp > SYSDATE - 7
GROUP BY EXTRACT(HOUR FROM timestamp)
ORDER BY hour_of_day;

Automated Alerts

#!/bin/bash
# Alert script for excessive session kills
KILLS=$(sqlplus -s / as sysdba << EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
SELECT COUNT(*)
FROM v$diag_alert_ext
WHERE message_text LIKE '%session%kill%'
AND timestamp > SYSDATE - 1/24;
EOF
)

if [ "$KILLS" -gt 10 ]; then
    echo "ALERT: $KILLS sessions killed in the last hour" | \
    mail -s "High Session Kill Rate" [email protected]
fi

Related Errors

• ORA-00018: Maximum number of sessions exceeded
• ORA-01012: Not logged on
• ORA-03113: End-of-file on communication channel
• ORA-03114: Not connected to ORACLE

Best Practices

1. Implement proper connection retry logic
2. Monitor resource usage and limits
3. Use appropriate transaction sizes
4. Set reasonable profile limits
5. Regular monitoring of session kills
6. Proper application error handling

Troubleshooting Checklist

• Verify session reconnection capability
• Check alert log for kill reasons
• Review resource limit settings
• Analyze transaction patterns
• Implement retry logic in applications
• Monitor session kill frequency
• Verify data consistency post-kill
• Update application error handling