Ora-01012 Not Logged On: Instant Fix for Oracle Session Errors

Staring at the "ora-01012: not logged on" error after a password reset or listener hiccup? ⚡ Oracle’s way of saying "nope, not happening" to your login attempt. The fix isn’t always obvious—sometimes it’s a typo, sometimes it’s a service that needs a restart.

The most common culprits are wrong credentials, an expired password, or a listener that’s not playing nice. I’ve seen this error after migrating databases, too—where the connection strings get silently mangled.

The good news? Most fixes take under five minutes once you know where to look. Start with the basics: verify your username/password, then check if the Oracle service is running.

You’ll walk away with a working connection in no time—whether it’s SQL*Plus, SQL Developer, or a third-party tool. I’ll show you the exact commands to test your listener, how to reset passwords without admin access, and why "ORACLE" in all caps might be your problem.

Let’s get this session back online.

Works for Oracle 12c through 23c, on Windows or Linux. No special tools needed—just your command line and a little patience. Here’s where we begin troubleshooting.

Root Causes Of Session Failures

When you encounter an Oracle error like ORA-01012: not logged on, it’s typically a sign that your database session isn’t properly authenticated or connected. This can happen due to a mix of technical misconfigurations, user errors, or system-level issues.

Below, we break down the most common reasons—so you can pinpoint the exact cause and resolve it faster.

###

🔒 Invalid or Expired Credentials

Oracle requires valid login credentials (username and password) to establish a session. If these are incorrect, expired, or locked, Oracle will reject the connection attempt.

• Incorrect Password: A simple typo or case-sensitivity issue (Oracle passwords are case-sensitive) can trigger this error. For example, entering USERNAME instead of username may fail silently.
• Expired Password: Some Oracle environments enforce password expiration policies. If a user’s password has expired, they’ll get this error even if the credentials are correct.
• Account Locked: After multiple failed login attempts, Oracle may lock the account temporarily or permanently, blocking further access.

How to check: Verify the account status in DBAUSERS or USERUSERS views. Run:

SELECT username, accountstatus FROM dbausers WHERE username = '<yourusername>';

Debug tip: Test connectivity with tnsping <servicename> or telnet <hostname> 1521. If these fail, the issue is network-related.

###

🔄 Session Timeout or Idle Disconnection

Oracle sessions can terminate due to inactivity or server-side timeouts, especially in shared environments like cloud databases or connection pools.

• Idle Timeout: Many Oracle setups (e.g., Oracle RAC, cloud databases) enforce idle session timeouts (e.g., 30 minutes). If you don’t execute a query, the session drops.
• Connection Pool Exhaustion: Applications using connection pools (like Java’s OracleDataSource) may return stale or closed connections, leading to this error.
• Server-Side Termination: Database admins may manually kill idle sessions via ALTER SYSTEM KILL SESSION.

Fix: Reconnect using a fresh session or adjust the timeout settings in your application’s connection pool (e.g., validationQuery in Tomcat/JDBC).

###

🛠️ Database or Service Unavailability

Sometimes, the error isn’t user-related—it’s a server issue. If the Oracle database is down, restarting, or in nomount/mount mode, new connections will fail.

• Database Shutdown: Check the database status with SQL*Plus or lsnrctl status. If the listener isn’t running, no connections will work.
• Instance Crash: A hung Oracle process (e.g., ORACLE) may leave the database unreachable until restarted.
• Storage or Resource Issues: Disk failures, memory swapping, or CPU overload can cause the database to reject connections.

Quick check: Run ps -ef | grep ora (Linux) or check Windows Services for Oracle-related processes. If the listener (tnslsnr) isn’t running, start it with:

lsnrctl start

• ORACLESID Mismatch: If you’re connecting to the wrong ORACLE_SID (e.g., ORCL instead of PROD), Oracle may return this error.
• User Session Limits: Some databases enforce SESSIONS or PROCESSES limits. Hitting the cap blocks new logins.
• Shared Server Issues: In shared server mode, a misconfigured DISPATCHERS pool can cause connection drops.

Verify with:

SHOW PARAMETER sessions processes
If limits are too low, adjust them in spfile or init.ora.

Quick Fixes for Oracle Login Errors

Encountering the Oracle "not logged on" error (ORA-01012) can disrupt workflows, but the right fixes can resolve it fast. Below are targeted solutions mapped to common causes—test them one by one until the issue resolves.

🔧 Fix 1: Verify and Reconnect Your Session

If your connection dropped unexpectedly, a simple reconnect often works.

• 🔄 Restart your Oracle client tool (e.g., SQLPlus, SQL Developer, or Toad). Close and reopen the application.
• 🔒 Check your credentials—typos in username/password or expired passwords trigger this error. Reset if needed.
• ⏰ Wait 5–10 minutes—some Oracle environments enforce session timeouts. Log back in after the delay.

• 📝 Check profile settings: SELECT FROM DBAPROFILES WHERE USERNAME = 'yourusername';
• 🔄 Adjust limits (as admin): ALTER PROFILE default LIMIT PASSWORDLIFETIME UNLIMITED; ALTER PROFILE default LIMIT SESSIONS_PER_USER UNLIMITED;