Adamant Solutions

Oracle ORA- error explainer

Paste a stack trace or log line, or type an error code. You get the likely causes in order of frequency, what to check, and read-only SQL to find the culprit. Runs in your browser; we only count anonymous visits.

All errors (38)

ORA-00001unique constraint (…) violated

Likely causes, most frequent first

  • An insert or update repeats a key that already exists.
  • A sequence was reset or lags behind the data (data load, restore, copy between environments), so it hands out values already used.
  • Two sessions insert the same key at nearly the same time.
  • A retry re-sends an insert that had already succeeded.

What to check

  • Find the constraint named in the message and the columns it covers.
  • Compare the sequence's last value with MAX(id) of the table.
  • Use MERGE or handle the exception when duplicates are expected.

Diagnostic SQL

SELECT c.constraint_name, c.table_name, c.constraint_type, cc.column_name
FROM all_constraints c JOIN all_cons_columns cc USING (owner, constraint_name)
WHERE c.constraint_name = 'CONSTRAINT_NAME_FROM_THE_MESSAGE';

Oracle documentation for ORA-00001

ORA-00018maximum number of sessions exceeded

Likely causes, most frequent first

  • A connection leak: the application opens sessions and does not close them.
  • Connection pools of several applications add up beyond the SESSIONS parameter.
  • The SESSIONS limit is too low for the real workload.

What to check

  • See who holds the sessions, grouped by user, machine and program.
  • Compare current and maximum utilization with the limit.
  • Fix the leak first; raising SESSIONS only delays the problem.

Diagnostic SQL

SELECT username, machine, program, COUNT(*) AS sessions
FROM v$session GROUP BY username, machine, program ORDER BY sessions DESC;

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit WHERE resource_name IN ('sessions','processes');

Oracle documentation for ORA-00018

ORA-00020maximum number of processes (…) exceeded

Likely causes, most frequent first

  • Too many dedicated server processes: pools, batch jobs or a connection leak.
  • The PROCESSES parameter is too low (SESSIONS is derived from it).

What to check

  • Check utilization of processes and sessions.
  • Reduce pool sizes across applications; consider a connection pooler such as DRCP.
  • Raising PROCESSES needs a restart.

Diagnostic SQL

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit WHERE resource_name IN ('processes','sessions');

Oracle documentation for ORA-00020

ORA-00054resource busy and acquire with NOWAIT specified or timeout expired

Likely causes, most frequent first

  • DDL (ALTER TABLE, CREATE INDEX) or SELECT … FOR UPDATE NOWAIT on a table another session has locked.
  • An open transaction that was never committed (often an idle session in a pool or IDE).

What to check

  • Find the session holding the lock and what it is doing.
  • Commit or roll back the blocking transaction, or run the DDL when the table is quiet.
  • ALTER SESSION SET ddl_lock_timeout lets DDL wait for the lock instead of failing immediately.

Diagnostic SQL

SELECT s.sid, s.serial#, s.username, s.status, s.program, o.object_name
FROM v$locked_object l
JOIN dba_objects o ON o.object_id = l.object_id
JOIN v$session s ON s.sid = l.session_id;

ALTER SESSION SET ddl_lock_timeout = 30;

Oracle documentation for ORA-00054

ORA-00060deadlock detected while waiting for resource

Likely causes, most frequent first

  • Two sessions each hold a lock the other needs because they lock rows in opposite order.
  • Foreign key columns without an index make parent changes lock the whole child table.
  • Bitmap indexes or ITL waits in busy blocks (less common).

What to check

  • Read the deadlock graph in the trace file named in the alert log.
  • Make all code paths lock tables and rows in the same order.
  • Index foreign key columns.
  • Retry the transaction in the application: one of the sessions is rolled back by design.

Oracle documentation for ORA-00060

ORA-00904invalid identifier

Likely causes, most frequent first

  • A misspelled column or alias.
  • A column created with quotes in lower or mixed case ("Name") but referenced without quotes.
  • The column does not exist in this environment or version of the schema.
  • An alias used in WHERE or in the same SELECT list where it is not visible yet.

What to check

  • Describe the table in the schema you are connected to.
  • Check the exact case with the data dictionary.

Diagnostic SQL

SELECT column_name FROM all_tab_columns
WHERE owner = 'SCHEMA' AND table_name = 'TABLE_NAME' ORDER BY column_id;

Oracle documentation for ORA-00904

ORA-00911invalid character

Likely causes, most frequent first

  • A semicolon at the end of a statement sent through JDBC, ODBC or another driver (the semicolon belongs to SQL*Plus, not to the SQL).
  • Smart quotes or other non-ASCII characters pasted from a document.
  • A stray character in a dynamic SQL string.

What to check

  • Remove the trailing semicolon from statements in application code (not from PL/SQL blocks that end with END;).
  • Replace curly quotes with plain ones.

Oracle documentation for ORA-00911

ORA-00933SQL command not properly ended

Likely causes, most frequent first

  • A trailing semicolon or an extra clause in a JDBC statement.
  • Syntax from another database (LIMIT, TOP, ILIKE) that Oracle does not support. Use FETCH FIRST n ROWS ONLY or ROWNUM.
  • ORDER BY or JOIN syntax in the wrong place, or an UPDATE with a JOIN written the MySQL way.

What to check

  • Read the statement up to the position the error points to.
  • Use FETCH FIRST (12c and later) for limits.

Oracle documentation for ORA-00933

ORA-00942table or view does not exist

Likely causes, most frequent first

  • The table is in another schema and the name is not prefixed or has no synonym.
  • The user has no privilege, or the privilege comes from a role: roles are not active inside definer-rights PL/SQL, so grant directly.
  • Connected to the wrong database, PDB or environment.
  • The object name was created with quotes and a different case.

What to check

  • Search the data dictionary for the name.
  • Check which user you are connected as and what you can see.

Diagnostic SQL

SELECT owner, object_name, object_type FROM all_objects
WHERE object_name = UPPER('NAME');

SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS me,
       SYS_CONTEXT('USERENV','CON_NAME')     AS container FROM dual;

Oracle documentation for ORA-00942

ORA-00972identifier is too long

Likely causes, most frequent first

  • An object name longer than the limit (30 bytes before 12.2, 128 bytes from 12.2 with compatibility set accordingly).
  • Multibyte characters count as several bytes.

What to check

  • Shorten the name; generated names from ORMs are a frequent source.

Oracle documentation for ORA-00972

ORA-01000maximum open cursors exceeded

Likely causes, most frequent first

  • A cursor leak: JDBC Statement, PreparedStatement or ResultSet objects that are not closed.
  • OPEN_CURSORS is too low for the number of statements a session keeps open.
  • Statement caching keeps many cursors open per connection in a large pool.

What to check

  • See which sessions hold the most open cursors.
  • Close resources reliably (try-with-resources in Java).
  • Raise OPEN_CURSORS only after the leak is fixed.

Diagnostic SQL

SELECT s.sid, s.username, s.program, a.value AS open_cursors
FROM v$sesstat a JOIN v$statname b ON a.statistic# = b.statistic#
JOIN v$session s ON s.sid = a.sid
WHERE b.name = 'opened cursors current' ORDER BY a.value DESC;

SHOW PARAMETER open_cursors

Oracle documentation for ORA-01000

ORA-01017invalid username/password; logon denied

Likely causes, most frequent first

  • Wrong password (passwords are case sensitive in 11g and later by default).
  • Special characters in the password that the shell or tool interprets, so a different value is sent.
  • Connected to the wrong database or PDB, where the user does not exist.
  • An old password in a configuration file or a secrets store.
  • Mixed old and new clients and verifier types after a password policy change.

What to check

  • Try the same credentials with SQL*Plus or another client on the same service name.
  • Quote the password where needed.
  • Confirm the user exists in the container you connect to.

Oracle documentation for ORA-01017

ORA-01031insufficient privileges

Likely causes, most frequent first

  • The user lacks the system or object privilege for the statement.
  • The privilege comes from a role, which is not active in PL/SQL definer-rights procedures.
  • Connecting AS SYSDBA without the right operating-system group or password file entry.

What to check

  • Check the privileges the user holds.
  • Grant the privilege directly to the user for stored procedures.

Diagnostic SQL

SELECT * FROM session_privs ORDER BY privilege;
SELECT * FROM session_roles;

Oracle documentation for ORA-01031

ORA-01033ORACLE initialization or shutdown in progress

Likely causes, most frequent first

  • The instance is starting or stopping.
  • The database is mounted but not open.
  • In a container database, the PDB is not open.

What to check

  • Check the instance status and the PDB open mode.
  • Look at the alert log for recovery or startup errors.

Diagnostic SQL

SELECT instance_name, status, database_status FROM v$instance;
SELECT name, open_mode FROM v$pdbs;

Oracle documentation for ORA-01033

ORA-01034ORACLE not available

Likely causes, most frequent first

  • The instance is not started.
  • ORACLE_SID or ORACLE_HOME point to another instance or are not set for the process.
  • The instance died: check memory settings and the alert log.

What to check

  • Check whether the background processes (pmon) are running.
  • Check environment variables of the process that fails to connect.

Oracle documentation for ORA-01034

ORA-01400cannot insert NULL into (…)

Likely causes, most frequent first

  • A NOT NULL column is missing from the insert or receives a null from the application.
  • No default value is defined for the column.
  • A mapping or ORM sends null for a field you assumed was filled.

What to check

  • Read the column named in the message.
  • Add a default, fix the mapping or make the column nullable only if the data model allows it.

Oracle documentation for ORA-01400

ORA-01403no data found

Likely causes, most frequent first

  • A SELECT … INTO in PL/SQL returned no row (raises NO_DATA_FOUND).
  • A function used in SQL returned this exception.

What to check

  • Handle NO_DATA_FOUND in a block, or use a cursor or aggregate that returns one row.

Oracle documentation for ORA-01403

ORA-01422exact fetch returns more than requested number of rows

Likely causes, most frequent first

  • A SELECT … INTO in PL/SQL returned more than one row (TOO_MANY_ROWS).
  • Missing filter or duplicate data that should be unique.

What to check

  • Add the missing condition or a unique constraint, or use a cursor loop.

Oracle documentation for ORA-01422

ORA-01438value larger than specified precision allowed for this column

Likely causes, most frequent first

  • A number has more digits than the column NUMBER(p,s) allows, for example 1000 in NUMBER(3).
  • A scale smaller than expected rounds and then overflows.

What to check

  • Check the column definition and the value sent by the application.

Oracle documentation for ORA-01438

ORA-01555snapshot too old

Likely causes, most frequent first

  • A long-running query needs undo data that has already been overwritten.
  • The undo tablespace or UNDO_RETENTION is too small for the longest query.
  • Committing inside a loop that fetches from an open cursor on the same data.

What to check

  • Run long reports at quiet times or tune the query so it finishes faster.
  • Check UNDO_RETENTION and the undo tablespace size.
  • Do not commit inside a fetch loop over the same tables.

Diagnostic SQL

SHOW PARAMETER undo
SELECT MAX(maxquerylen) AS longest_query_seconds FROM v$undostat;

Oracle documentation for ORA-01555

ORA-01653unable to extend table … by … in tablespace …

Likely causes, most frequent first

  • The tablespace is full and its data files cannot grow (no autoextend or at maximum size).
  • The file system or ASM disk group is full.

What to check

  • See free space per tablespace and whether files can autoextend.
  • Add a data file or resize, or archive and purge old data.
  • ORA-01654 is the same problem for an index.

Diagnostic SQL

SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space GROUP BY tablespace_name;

SELECT file_name, autoextensible, ROUND(maxbytes/1024/1024) AS max_mb
FROM dba_data_files WHERE tablespace_name = 'TABLESPACE_FROM_THE_MESSAGE';

Oracle documentation for ORA-01653

ORA-01722invalid number

Likely causes, most frequent first

  • A non-numeric string is converted to a number, often implicitly when a VARCHAR2 column is compared with a number.
  • Mixed data in a column and the optimizer evaluates the conversion on rows you did not expect.
  • Decimal separator or grouping characters that do not match the NLS settings.

What to check

  • Compare like with like: convert explicitly with TO_NUMBER and a format, or compare as text.
  • Find the offending rows with a validating function or a pattern check.

Oracle documentation for ORA-01722

ORA-01795maximum number of expressions in a list is 1000

Likely causes, most frequent first

  • An IN list holds more than 1000 literal values.

What to check

  • Split into chunks combined with OR, use tuples such as (1, x) IN ((1, 10), (1, 11)), or put the values in a temporary table or a collection and join.

Oracle documentation for ORA-01795

ORA-02291integrity constraint (…) violated - parent key not found

Likely causes, most frequent first

  • A child row refers to a parent key that does not exist yet.
  • Rows are loaded in the wrong order, or the parent insert is not committed or failed earlier.

What to check

  • Insert the parent first, or fix the foreign key value.

Oracle documentation for ORA-02291

ORA-02292integrity constraint (…) violated - child record found

Likely causes, most frequent first

  • Deleting or changing a parent row that still has child rows.

What to check

  • Delete the children first, or define ON DELETE CASCADE where it is the right business rule.

Oracle documentation for ORA-02292

ORA-03113end-of-file on communication channel

Likely causes, most frequent first

  • A firewall or load balancer dropped an idle connection.
  • The server process died (crash, kill, resource limit); the alert log shows why.
  • A network interruption between client and server.

What to check

  • Read the alert log and trace files at the time of the error.
  • Make the pool's connection lifetime and validation shorter than the firewall idle timeout; enable TCP keepalive.
  • ORA-03114 (not connected to ORACLE) is the follow-up error on a dead connection.

Oracle documentation for ORA-03113

ORA-04031unable to allocate … bytes of shared memory

Likely causes, most frequent first

  • Shared pool fragmentation, often caused by statements built with literals instead of bind variables.
  • The SGA or shared pool is too small for the workload.

What to check

  • Use bind variables.
  • Review SGA sizing and automatic memory settings; check memory advisors.

Oracle documentation for ORA-04031

ORA-06502PL/SQL: numeric or value error

Likely causes, most frequent first

  • A string assigned to a variable that is too small, including byte versus character semantics with multibyte data.
  • A conversion that fails inside PL/SQL.

What to check

  • Enlarge the variable or declare it with %TYPE.

Oracle documentation for ORA-06502

ORA-06512at "…", line …

Likely causes, most frequent first

  • This is not the cause: it is a line of the call stack that follows the real error.

What to check

  • Read the first ORA- error in the stack; ORA-06512 lines show where it happened, innermost first.

Oracle documentation for ORA-06512

ORA-12154TNS:could not resolve the connect identifier specified

Likely causes, most frequent first

  • The name is not in tnsnames.ora, or the file is not found (TNS_ADMIN, ORACLE_HOME).
  • A typo or a hidden character in the connect identifier.
  • sqlnet.ora NAMES.DIRECTORY_PATH does not include TNSNAMES.
  • A JDBC URL in the wrong format.

What to check

  • In JDBC use the form jdbc:oracle:thin:@//host:port/service_name.
  • Test the name with tnsping.
  • Check which tnsnames.ora the process really reads.

Oracle documentation for ORA-12154

ORA-12170TNS:Connect timeout occurred

Likely causes, most frequent first

  • A firewall or security group blocks the port.
  • Wrong host or port, or the host is not reachable from this network.
  • A very slow network or an overloaded listener.

What to check

  • Test reachability of host and port from the client machine (tnsping, nc, Test-NetConnection).
  • Check firewall rules and the listener status.

Oracle documentation for ORA-12170

ORA-12505TNS:listener does not currently know of SID given in connect descriptor

Likely causes, most frequent first

  • The connection uses a SID but the database is registered only with a service name (common with pluggable databases).
  • The database has not registered with the listener yet.

What to check

  • Use the service name instead of the SID in the connect descriptor.
  • List the services the listener knows: lsnrctl services.

Oracle documentation for ORA-12505

ORA-12514TNS:listener does not currently know of service requested in connect descriptor

Likely causes, most frequent first

  • The service name in the connection is wrong or has a different case or domain.
  • The database or PDB has not registered its service with the listener yet (just started, or LOCAL_LISTENER misconfigured).
  • A PDB is not open.

What to check

  • Run lsnrctl services and compare with the service name you use.
  • Check the SERVICE_NAMES and LOCAL_LISTENER parameters; ALTER SYSTEM REGISTER forces registration.

Diagnostic SQL

SHOW PARAMETER service_names
SHOW PARAMETER local_listener
SELECT name, open_mode FROM v$pdbs;

Oracle documentation for ORA-12514

ORA-12519TNS:no appropriate service handler found

Likely causes, most frequent first

  • The listener has no free handler: the database reached its PROCESSES limit.
  • The service is blocked or the instance is not accepting connections.

What to check

  • Check processes utilization (see ORA-00020) and the listener status.

Oracle documentation for ORA-12519

ORA-12541TNS:no listener

Likely causes, most frequent first

  • The listener is not running.
  • Wrong host or port in the connection.
  • The listener listens on another interface or port.

What to check

  • Run lsnrctl status on the server.
  • Compare host and port with the listener configuration.

Oracle documentation for ORA-12541

ORA-12560TNS:protocol adapter error

Likely causes, most frequent first

  • A local (bequeath) connection with ORACLE_SID not set, or the database service is not running (typical on Windows).
  • A wrong ORACLE_HOME in the environment.

What to check

  • Set ORACLE_SID, or connect with a full connect string over the listener.

Oracle documentation for ORA-12560

ORA-28000the account is locked

Likely causes, most frequent first

  • Too many failed logins locked the account (FAILED_LOGIN_ATTEMPTS in the profile).
  • An application with an old password keeps retrying and locks it again after every unlock.
  • The account was locked by an administrator.

What to check

  • Find and fix the source of the wrong password first, then unlock.
  • Check the account status and profile limits.

Diagnostic SQL

SELECT username, account_status, lock_date, profile FROM dba_users
WHERE username = 'USERNAME';

ALTER USER username ACCOUNT UNLOCK;

Oracle documentation for ORA-28000

ORA-28001the password has expired

Likely causes, most frequent first

  • The profile's PASSWORD_LIFE_TIME has passed.

What to check

  • Change the password and update every application that uses it.
  • For service accounts, follow your security policy about profile settings and secret rotation.

Diagnostic SQL

SELECT username, account_status, expiry_date, profile FROM dba_users
WHERE username = 'USERNAME';

Oracle documentation for ORA-28001

How to use this tool

  1. Paste the error text. Every ORA- code in it is listed in order, with the first one usually being the cause and ORA-06512 lines only showing where it happened.
  2. Open an entry and go through the causes from the top.
  3. Run the diagnostic SQL with a user that can read the data dictionary views (V$ and DBA_ views need privileges). The queries only read; the ALTER lines are examples for you to run deliberately.

Example

A Java application reports ORA-12514: TNS:listener does not currently know of service requested. The entry lists the usual causes: a wrong service name, a database or PDB that has not registered with the listener, or a closed PDB, and the commands to confirm each.

Limits

The content is curated from common experience and is not a replacement for the official Oracle Database Error Messages documentation or for the alert log, which contains the exact cause for your instance. Messages and limits differ between versions. Causes are ordered by how often we see them, not by certainty.

Privacy and security of what you enter

Text you paste is searched in your browser and never sent anywhere. Remove passwords and host names from logs anyway; the log redactor can help. See the privacy notice.

Recurring database errors in production?

We review Java and Oracle setups, connection pools and gateways. See the JVM and pool sizing, the services or the OAuth2 and CORS error guides.

Message us on LinkedIn

Privacy and cookies