DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01012 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01012: not logged on — Causes, Fixes, and Prevention

ORA-01012 is an Oracle database error that occurs when a SQL operation is attempted on a session that is no longer connected to the database. This typically happens when a connection that has already been terminated — either by the server, a DBA, or a network interruption — is reused without re-establishing the session. It is one of the most common errors in connection pool environments and long-running application servers.

Top 3 Causes

1. Reusing a Dead Connection from the Connection Pool

Connection pools (HikariCP, DBCP, UCP) maintain a set of open connections. If Oracle or a firewall silently drops a connection while the pool still considers it valid, the next attempt to use that connection throws ORA-01012.

-- Check for KILLED or SNIPED sessions on the database side
SELECT sid,
       serial#,
       username,
       status,
       last_call_et,
       machine,
       program
FROM   v$session
WHERE  status IN ('KILLED', 'SNIPED')
ORDER  BY last_call_et DESC;

-- Identify long-idle sessions (idle > 1 hour)
SELECT sid,
       serial#,
       username,
       ROUND(last_call_et / 3600, 2) AS idle_hours,
       machine
FROM   v$session
WHERE  last_call_et > 3600
  AND  username IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

2. Session Killed by a DBA

When a DBA forcibly terminates a session using ALTER SYSTEM KILL SESSION, any subsequent SQL execution on that same connection by the application will result in ORA-01012.

-- Kill a specific session immediately
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

-- Verify the session is fully removed
SELECT sid, serial#, status
FROM   v$session
WHERE  sid = &killed_sid;
-- No rows = fully gone | KILLED status = rollback still in progress

-- Alternative: disconnect gracefully after current transaction
ALTER SYSTEM DISCONNECT SESSION '&sid,&serial#' POST_TRANSACTION;
Enter fullscreen mode Exit fullscreen mode

3. Explicit logoff Followed by Continued Use in Application Code

In OCI or JDBC-based applications, if a connection is explicitly closed (connection.close()) but the connection object is mistakenly reused afterward — especially in multi-threaded environments — ORA-01012 will be raised.

-- Find sessions opened by a specific application user
SELECT machine,
       program,
       status,
       COUNT(*) AS session_count
FROM   v$session
WHERE  username = '&APP_USER'
GROUP  BY machine, program, status
ORDER  BY session_count DESC;

-- Review recent SQL activity for the user
SELECT s.sid,
       s.username,
       s.status,
       q.sql_text
FROM   v$session s
JOIN   v$sql     q ON s.sql_id = q.sql_id
WHERE  s.username = '&APP_USER';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1: Confirm the connection is truly dead by querying V$SESSION.

Step 2: Force-close and remove the bad connection from the pool.

Step 3: Configure a lightweight validation query on borrow in your connection pool settings:

-- Use this as your pool validation query
SELECT 1 FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Step 4: Set SQLNET.EXPIRE_TIME in sqlnet.ora to detect dead connections at the network level:

-- sqlnet.ora setting (check every 10 minutes)
-- SQLNET.EXPIRE_TIME = 10

-- Enforce idle session limits via Oracle Profile
CREATE PROFILE app_profile LIMIT
    IDLE_TIME    30
    CONNECT_TIME 480;

ALTER USER app_user PROFILE app_profile;

-- Confirm profile assignment
SELECT username, profile
FROM   dba_users
WHERE  username = 'APP_USER';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enable Connection Validation in Your Pool
Always configure testOnBorrow=true and a validationQuery in your connection pool. For Oracle UCP, use setValidateConnectionOnBorrow(true). This ensures dead connections are detected and replaced before they reach application code, eliminating ORA-01012 at the source.

2. Implement Robust Exception Handling with Reconnect Logic
Catch ORA-01012 (error code 1012), ORA-03113, and ORA-03114 as connection-level errors. Upon catching these, immediately evict the connection from the pool and retry with a fresh one. Never attempt to reuse a connection after receiving any of these errors. Use SQLNET.EXPIRE_TIME alongside profile-level IDLE_TIME to keep both client and server in sync about connection state.

Related Oracle Errors

Error Code Description
ORA-03113 End-of-file on communication channel — network drop
ORA-03114 Not connected to Oracle — similar to ORA-01012
ORA-00028 Your session has been killed — precedes ORA-01012
ORA-02396 Exceeded maximum idle time — leads to ORA-01012 on reuse

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)