ORA-01012: Not Logged On - Fix Oracle Session Errors

ORA-01012: Not Logged On

Error Overview

Error Text: ORA-01012: not logged on

The ORA-01012 error occurs when an application attempts to execute a database operation against a session that is no longer connected to Oracle. The session has been terminated — either by a DBA, a network interruption, an idle timeout policy, or an abnormal process exit — and the client-side connection handle is now invalid. Any subsequent call through that handle raises ORA-01012.

This error is most commonly seen in connection pools where a pooled connection has silently gone stale, and the pool hands it to an application thread without first validating it.

Common Causes

1. Session Killed by a DBA

A DBA issued ALTER SYSTEM KILL SESSION or ALTER SYSTEM DISCONNECT SESSION targeting the session. The server-side session is marked killed immediately; any in-flight or subsequent client call receives ORA-01012.

2. Network Interruption

A firewall, load balancer, or network device silently dropped the TCP connection. Oracle’s server process may still exist in a SNIPED or killed state while the client-side driver holds a dangling socket.

3. Idle Timeout via SQLNET.EXPIRE_TIME / Dead Connection Detection

When SQLNET.EXPIRE_TIME is configured in sqlnet.ora, Oracle sends periodic probe packets. If the client is unreachable the session is terminated. Similarly, profiles with IDLE_TIME limits disconnect idle sessions.

4. Application Connection Pool Stale Connections

J2EE / JDBC connection pools, ODP.NET pools, and similar middleware cache connections. If the pool does not validate connections on borrow, a recycled connection that was killed server-side will fail on first use with ORA-01012.

5. Process or OS-Level Termination

The Oracle server process (shadow process) was killed at the OS level (kill -9), or the database instance was bounced while the client still held the connection handle.

6. Profile-Based Resource Limits

A user profile with SESSIONS_PER_USER, CONNECT_TIME, or IDLE_TIME limits can force disconnection when limits are breached.

Diagnostic Queries

Check for Recently Killed Sessions

-- Sessions in KILLED or SNIPED state
SELECT sid, serial#, username, status, osuser, machine,
       last_call_et, program
FROM v$session
WHERE status IN ('KILLED', 'SNIPED')
ORDER BY last_call_et DESC;

Check Active Profile Limits

-- Review resource limits for a user
SELECT username, profile FROM dba_users WHERE username = UPPER('&username');

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = UPPER('&username'))
  AND resource_type = 'KERNEL';

Check sqlnet.ora Timeout Parameters

-- View network-level parameters from the instance perspective
SELECT name, value
FROM v$parameter
WHERE name IN ('sqlnet.expire_time', 'tcp.connect_timeout',
               'inbound_connect_timeout');

-- Check profile-based idle time
SELECT p.profile, p.resource_name, p.limit
FROM dba_profiles p
WHERE p.resource_name IN ('IDLE_TIME', 'CONNECT_TIME', 'SESSIONS_PER_USER')
ORDER BY p.profile, p.resource_name;

Identify the Killing Session from Audit Trail

-- Audit trail for session disconnects (if auditing enabled)
SELECT db_user, os_user, userhost, action_name,
       TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS event_time,
       obj_name, comment$text
FROM dba_audit_trail
WHERE action_name IN ('LOGOFF BY CLEANUP', 'SESSION REC')
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

Check Alert Log for Recent Kills

-- Use external table or ADRCI to scan alert log
-- Quick view via V$DIAG_ALERT_EXT (12c+)
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%killed%'
   OR message_text LIKE '%ORA-01012%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

Validate Dead Connection Detection Settings

-- Check if Dead Connection Detection is configured
-- sqlnet.ora on the server should contain SQLNET.EXPIRE_TIME=10 (minutes)
-- View OS-level file if accessible:
-- $ORACLE_HOME/network/admin/sqlnet.ora

SELECT name, value
FROM v$parameter
WHERE LOWER(name) LIKE '%expire%'
   OR LOWER(name) LIKE '%dead%';

Step-by-Step Resolution

1. Immediate Recovery — Application Side

If your application receives ORA-01012, the connection must be discarded and a new one obtained:

// Java / JDBC example — discard and reconnect
try {
    stmt.executeQuery(sql);
} catch (SQLException e) {
    if (e.getErrorCode() == 1012) {
        connection.close();          // discard the stale connection
        connection = dataSource.getConnection();  // obtain a fresh one
        stmt = connection.prepareStatement(sql);
        stmt.executeQuery(sql);      // retry
    }
}

2. Enable Connection Validation in the Pool

For JDBC Universal Connection Pool (UCP):

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//host:1521/service");
// Validate connection before handing to application
pds.setValidateConnectionOnBorrow(true);
pds.setSQLForValidateConnection("SELECT 1 FROM DUAL");

For Oracle JDBC connection pool (ojdbc):

# In datasource configuration
oracle.jdbc.pool.OracleConnectionPoolDataSource.validateOnBorrow=true
oracle.jdbc.pool.OracleConnectionPoolDataSource.connectionValidationSQL=SELECT 1 FROM DUAL

3. Check and Restore the Session (DBA Action)

-- Confirm whether session is truly gone
SELECT sid, serial#, status, username, last_call_et
FROM v$session
WHERE username = UPPER('&username')
ORDER BY last_call_et;

-- If a KILLED session is blocking resources, force cleanup
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- If the shadow process is still OS-resident (hung kill), find the OS PID
SELECT spid FROM v$process
WHERE addr = (SELECT paddr FROM v$session WHERE sid = &sid);
-- Then at OS level: kill -9 <spid>

4. Increase Profile Idle Timeout (if too aggressive)

-- Check current setting
SELECT limit FROM dba_profiles
WHERE profile = 'DEFAULT' AND resource_name = 'IDLE_TIME';

-- Increase idle time to 60 minutes (value is in minutes)
ALTER PROFILE default LIMIT IDLE_TIME 60;

-- Or unlimited for service accounts (use with caution)
ALTER PROFILE app_profile LIMIT IDLE_TIME UNLIMITED;

5. Configure Dead Connection Detection

Add to $ORACLE_HOME/network/admin/sqlnet.ora on the database server:

# Probe idle connections every 10 minutes
SQLNET.EXPIRE_TIME = 10

This ensures Oracle actively detects and cleans up dead connections rather than leaving them as SNIPED sessions consuming resources.

6. Verify Firewall Timeout Alignment

If a firewall sits between application and database servers, ensure its TCP idle timeout is longer than SQLNET.EXPIRE_TIME. A firewall that kills the TCP socket before Oracle probes it will cause silent stale connections.

Prevention Strategies

1. Implement Robust Reconnect Logic

-- PL/SQL wrapper with reconnect detection
CREATE OR REPLACE PROCEDURE safe_execute(p_sql IN VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE p_sql;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1012 THEN
      -- Log the event and signal the caller to reconnect
      DBMS_OUTPUT.PUT_LINE('Session lost — ORA-01012. Reconnection required.');
      RAISE;
    ELSE
      RAISE;
    END IF;
END safe_execute;
/

2. Monitor Stale Session Accumulation

-- Schedule a job to report SNIPED/KILLED sessions
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_STALE_SESSIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_count NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_count
        FROM v$session
        WHERE status IN ('KILLED','SNIPED');
        IF v_count > 10 THEN
          -- Insert into monitoring table or send alert
          INSERT INTO dba_alerts(alert_time, alert_msg)
          VALUES (SYSDATE, 'Stale sessions: ' || v_count);
          COMMIT;
        END IF;
      END;
    ]',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
    enabled         => TRUE
  );
END;
/

3. Best Practices Summary

• Always validate connections on borrow in connection pools
• Configure SQLNET.EXPIRE_TIME on the database server to detect dead clients
• Align firewall TCP idle timeouts with SQLNET.EXPIRE_TIME
• Set sensible IDLE_TIME profile limits — not zero or unlimited extremes
• Log ORA-01012 occurrences in application error handlers to detect patterns
• Use Oracle’s Fast Application Notification (FAN) with FAN-aware drivers to receive instant disconnect events

Related Errors

• ORA-01034 - Oracle Not Available (instance is down)
• ORA-01109 - Database Not Open
• ORA-02063 - Database Link Error
• ORA-03113 - End-of-file on Communication Channel
• ORA-01017 - Invalid Username/Password (on reconnect)

Emergency Response

Quick Fixes

1. Clear all KILLED/SNIPED sessions

   BEGIN
     FOR s IN (SELECT sid, serial# FROM v$session
               WHERE status IN ('KILLED','SNIPED')) LOOP
       EXECUTE IMMEDIATE
         'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
     END LOOP;
   END;
   /

2. Force application reconnection by bouncing the pool

   -- In WebLogic / WAS admin console, shrink or restart the connection pool.
   -- For lightweight JDBC pools, set pool min size to 0 briefly:
   -- This evicts all cached connections and forces fresh logons.

3. Verify database availability before blaming the session

   SELECT status, open_mode FROM v$instance, v$database;

Post-Resolution Cleanup

-- Confirm no orphaned sessions remain
SELECT COUNT(*), status
FROM v$session
WHERE username IS NOT NULL
GROUP BY status;

-- Review alert log for root cause (network drops, OOM kills, etc.)
-- ADRCI: adrci> show alert -tail 100

-- Update sqlnet.ora if dead connection detection was missing
-- Then reload the listener: lsnrctl reload