Back to all blogs

Oracle Database Troubleshooting: How to Diagnose and Fix Common Errors

By SpeedContent Editorial
November 14, 2026

Google's top search for the error "ORA-00600" (or "0600") Results in a Blank Page According to Oracle, Oracle Database powers a large percentage of banking systems, airline reservation systems, hospital records, and retail applications around the globe. The vast majority of the time when the software is operating there will be no indication to an end user of its existence. It when it goes down or doesn't respond that it bogs down or halts an entire business. This guide to Oracle Database Troubleshooting: How to Diagnose and Fix Common Errors explains how to approach those failures with confidence.

The error codes seem arcane at first and honestly they are kind of scary on first sight.

Why Oracle Errors Look the Way They Do

Each Oracle error message begins with a prefix and number. The prefix explains which component caused the error, and the number indicates the particular problem. Here are a few prefixes that you'll encounter:

  • ORA-: Core database errors (the most common by far)
  • TNS-: Network and listener errors
  • SP2-: SQL*Plus tool errors
  • EXP-/IMP-: Legacy export and import utility errors
  • RMAN-: Backup and recovery manager errors
  • PLS-: PL/SQL compilation errors

So now that you can see "TNS-12541," you already understand this is a network issue and not a corrupted table. That tidbit saves you all of that fruitless labor.

A Reliable Oracle Database Troubleshooting Process

Random guessing is the problem. In the pressure of deployment, people hit "Restart service" or "Change parameter X to 12" or run a script they downloaded from the internet. Sometimes that's the answer.

Mostly it just makes the situation worse.

  1. Capture the exact error. Copy the full message, including numbers, file names, and timestamps. Don't paraphrase.
  2. Identify the scope. Does it affect one user, one application, or everyone?
  3. Check what changed. New patches, configuration edits, storage changes, and application releases are frequent culprits.
  4. Read the logs. The alert log usually tells the story.
  5. Search the error code. Oracle's documentation and My Oracle Support explain most codes in detail.
  6. Apply the least invasive fix first. Try the safest option before anything drastic.
  7. Document the result. Next time, you'll thank yourself.

Third step. This is worth a second look. I have found that many so-called "mysterious" failures can be tracked to changes that someone made one or two days before.

Where to Find Diagnostic Information

Oracle has a lot of details about its work. You just have to know where to look.

The Alert Log

The alert log is a text-based file of important database events: startup/shutdown, error conditions, log switches, and structural changes. It is almost always the first thing a DBA looks at. In recent releases it exists inside the Automatic Diagnostic Repository (ADR) - find the location with the following query:

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Look for the file named alert_<SID>.log in that directory.

Trace Files

In the event of a major incident, Oracle dumps a trace file containing all sorts of internal details. They're pretty dense files. Most people don't read them all line by line; they just upload them to Oracle Support or look for the error stack.

The ADRCI Tool

ADRCI is a command line utility used to view and package diagnostic data. Here are some useful commands:

  • show alert -tail 50 displays the last 50 alert log lines
  • show problem lists tracked problems
  • show incident lists individual incidents

Dynamic Performance Views

Views with a beginning of V$ are live information, such as V$SESSION for who is logged on, V$SQL for statements issued, and V$TABLESPACE for storage questions. They are a direct glimpse into the open database.

Common Oracle Database Troubleshooting Errors and Fixes

Ok, now the good stuff. These are the errors that you will see over and over, as well as practical solutions for them.

ORA-12154: TNS could not resolve the connect identifier

What it means: The client doesn't know what port to connect to. Oracle checked the service name you entered and couldn't find it. Causes:

  • A typo in the connection string
  • A missing or misplaced tnsnames.ora file
  • The TNS_ADMIN environment variable pointing somewhere unexpected
  • Multiple Oracle clients installed, with the wrong one being used

How to fix it:

  1. Double-check the service name spelling.
  2. Open tnsnames.ora and confirm the entry exists.
  3. Run tnsping <service_name> to test resolution.
  4. Check which Oracle home your application is actually using.

Or test the Easy Connect form and do without the name file: sqlplus user@//hostname:1521/servicename If that executes without error, then the error is surely in your name resolution.

ORA-12541: TNS: no listener

What it means: Something isn't listening at that address and port. Typical causes:

  • The listener isn't running
  • The wrong host or port is configured
  • A firewall is blocking traffic

How to fix it:

  • Run lsnrctl status on the database server.
  • If it's stopped, use lsnrctl start.
  • Confirm the port (1521 is default, but not guaranteed).
  • Ask your network team whether a firewall rule changed recently.

ORA-01017: Invalid username/password; logon denied

How do I know?

The login wasn't successful. For example, it could have been the password that was wrong. Or it could have been something more subtle.

  • Case-sensitive passwords (enabled by default since 11g)
  • An expired or locked account
  • Special characters needing quotation marks
  • Connecting to the wrong database

How to fix it:

  • Verify you're targeting the right instance.
  • Check account status with SELECT username, account_status FROM dba_users;
  • Reset the password if needed: ALTER USER scott IDENTIFIED BY newpassword;

After multiple failed attempts, an account can get locked out so no need to keep guessing.

ORA-28000: The account is locked

This is connected. Too many login attempts, or because your admin has decided to - an account is frozen. Unlock it with:

ALTER USER username ACCOUNT UNLOCK;

Perhaps something is retrying the old credentials, such as a script that was left running, a mobile app, or a scheduled job. Locate that source before unlocking over and over.

ORA-01555: Snapshot too old

What it indicates: A long-running query requested a previous state of data, but the state had been overwritten in the undo space by a previous transaction. Common reasons:

  • Undo retention set too low
  • Undo tablespace too small
  • Very long queries running while heavy updates occur
  • Frequent commits inside loops

How to fix it:

  • Increase UNDO_RETENTION.
  • Enlarge the undo tablespace.
  • Tune the slow query so it finishes sooner.
  • Avoid committing inside a cursor loop that's fetching from the same data.

It's always a tuning issue rather than a break/fix situation. It's just the one query that fails is the unlucky one.

ORA-01653 and ORA-01654: Unable to extend table or index

What it is: You've maxed out a tablespace. How to resolve it:

  1. Check usage:
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS mb_free
FROM dba_free_space
GROUP BY tablespace_name;
  1. Add a datafile:
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/users02.dbf' SIZE 1G AUTOEXTEND ON;
  1. Or resize an existing file if disk space allows.

Prevention is key here. Avoid this 2 a.m. phone call by monitoring tablespace usage and creating an alert at 80 or 85 percent.

ORA-04031: Unable to allocate shared memory

What it means: The memory pool or other memory area requested did not have a free region large enough to allocate from. Causes:

  • Undersized SHARED_POOL_SIZE or SGA_TARGET
  • Applications that don't use bind variables, flooding the pool with near-identical SQL statements
  • Memory fragmentation

How to fix it:

  • Review memory settings and consider increasing them.
  • Encourage bind variables in application code.
  • Look at V$SGASTAT for what's consuming the pool.

Memory up helps in the short term. For the long term, it's solving the way the application sends SQL.

ORA-00060: Deadlock detected

What it means:Two sessions each try to lock each other's resource. Oracle resolves the deadlock by rolling back one of the statements. How to resolve it:

  • Review the trace file named in the message; it shows the involved SQL.
  • Make sure applications access tables in a consistent order.
  • Keep transactions short.
  • Check for unindexed foreign keys, a classic hidden cause.

Deadlocks are almost always caused by your application design, not bugs in the database.

ORA-00054: Resource busy and acquire with NOWAIT specified

Another session is working on this resource. FInd out whatsession is blocking it:

SELECT blocking_session, sid, serial#, username
FROM v$session
WHERE blocking_session IS NOT NULL;

Next, either hold off or ask the other user to get off. One can kill a session (ALTER SYSTEM KILL SESSION 'sid, serial#'), but it's not something to do haphazardly because it rollback any uncommitted work.

ORA-03113: End-of-file on communication channel

What this indicates: There was an unexpected loss of communication between the client and server. Possible reasons are:

  • The server process crashed
  • Network interruption or timeout
  • A firewall killing idle connections
  • An underlying ORA-00600 or ORA-07445 error

How to fix it:

  • Check the alert log right away. The real cause is usually written there.
  • Test network stability.
  • Review firewall idle timeout settings.

It is in the log that the details reside, so the message itself is purposely unhelpful.

ORA-00600: Internal error code

This is the one that causes the panic. ORA-00600 is an internal error which may be a bug, corruption or strange condition. The first argument in brackets is key to the problem, for example, ORA-00600: internal error code, arguments: [kcbz_check_objd_typ] What to do:

  1. Don't panic, and don't start deleting files.
  2. Gather the alert log and the referenced trace file.
  3. Use the My Oracle Support ORA-600/7445 lookup tool with the first argument.
  4. Check for known bugs and available patches.
  5. Open a service request if there's no obvious match.

So you often can't learn how to fix it on your own. The aim: gather good evidence and escalate wisely.

ORA-07445: Exception encountered, core dump

ORA-00600 1. Same as ORA-00600, but due to an exception generated by the operating system in the Oracle process. Take the same action: diagnostics, search for matches, patch if appropriate.

ORA-01031: Insufficient privileges

You're doing something this account isn't permitted to do. Look into grants:

SELECT * FROM user_sys_privs;
SELECT * FROM user_role_privs;

Now ask you DBA the privilege you will need. It would be tempting to just grant DBA to fix all the permission problems. Do not do that; least privilege is better.

ORA-00257: Archiver error

Archive log destination is full. The database cannot archive log and will freeze. Urgent, but can be corrected.

Steps:

  • Check space with df -h (Linux) or disk tools on Windows.
  • Use RMAN to back up and delete old archive logs: DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2'; (after verifying backups!)
  • Check DB_RECOVERY_FILE_DEST_SIZE if you're using a fast recovery area.

Manual removal of archive logs from the operating system without informing RMAN will cause later confusion. Use RMAN.

Quick Reference Table

ErrorPlain MeaningFirst Thing to Check
ORA-12154Can't resolve service nametnsnames.ora, spelling
ORA-12541No listenerlsnrctl status
ORA-01017Bad credentialsPassword, case, instance
ORA-28000Locked accountdba_users status
ORA-01555Snapshot too oldUndo size and retention
ORA-01653Tablespace fulldba_free_space
ORA-04031Out of shared memorySGA settings, bind variables
ORA-00060DeadlockTrace file, access order
ORA-03113Connection lostAlert log
ORA-00600Internal errorTrace file, Oracle Support
ORA-00257Archiver stuckArchive destination space

Performance Problems: When Nothing Is "Broken" but Everything's Slow

Not all problems produce an error message. Some days the database is just slow and complaints happen. Now that is troubleshooting a little different.

  • Is the slowness constant or intermittent?
  • Is it one query or everything?
  • Did it begin after a data load, patch, or application release?

Useful tools include:

  • AWR (Automatic Workload Repository) reports: Snapshots of database activity over time. They require the Diagnostics Pack license, so confirm licensing first.
  • Statspack: A free alternative for older or unlicensed environments.
  • SQL Trace and TKPROF: Detailed timing for individual statements.
  • Execution plans: Use EXPLAIN PLAN or DBMS_XPLAN.DISPLAY_CURSOR to see how Oracle runs a query.

The usual suspects: missing indexes, stale optimizer statistics, full table scans on large tables and lock contention. Updating the statistics is a good cheap first test:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

If you can, test it out in a test environment first. But sometimes fresh stats can change the execution plan, which is a great thing, but "great" doesn't necessarily mean "always".

Mistakes to Avoid

Old hands have their complaints. Some can be summed up as follows:

  • Making changes without a backup. Always confirm a recent, restorable backup exists.
  • Testing in production. Use a development copy when possible.
  • Ignoring the alert log. It's right there, and it's free.
  • Fixing symptoms only. Restarting the database may clear a problem briefly while the root cause quietly waits.
  • Skipping documentation. Memory is unreliable at 3 a.m.
  • Applying random scripts from forums. Understand what a command does before running it.

Preventing Problems Before They Start

Learning how to solve problems is fantastic, but not having to start the fire is even better. Here are some things you should adopt:

  • Monitor tablespace, archive log, and disk usage with alerts.
  • Schedule regular, tested backups (testing restores is the part people skip).
  • Keep Oracle patched using Release Updates, following a proper change process.
  • Gather optimizer statistics on a regular schedule.
  • Review the alert log routinely, not just during emergencies.
  • Maintain a runbook with common errors and approved fixes.
  • Use a test environment that mirrors production.

It's not glamorous. But it's what separates a quiet week from an all-hands-on-deck emergency.

Key Takeaways

  • Oracle error codes follow patterns; the prefix points to the component involved.
  • Follow a process: capture the error, define scope, check recent changes, read logs, then apply the least invasive fix.
  • The alert log and trace files hold most of the answers.
  • Many common errors (ORA-12154, ORA-12541, ORA-01017) are configuration or connection issues, not database damage.
  • Space-related errors like ORA-01653 and ORA-00257 are best prevented through monitoring.
  • ORA-00600 and ORA-07445 usually require Oracle Support; collect diagnostics rather than experimenting.
  • Always back up before making changes, and document what you did.

Frequently Asked Questions

Q: What's the first thing I should do when I encounter an ORA- error?

A: Log the entire message, then look at the alert log for the time period around the occurrence. You will usually see providing clues to the message in the entries that surround the error.

Q: Can I repair Oracle mistakes even if I'm not a DBA?

A: Some, yes. Connection and other simple permission issues are often a snap, but internal error and recovery issues should be reserved for someone with experience.

Q: Can I re-boot the database as a remedy for the mistake?

A: It sometimes resolves an interim problem but may also mask the cause, break users and if you're unlucky cause even more difficulties when you finally clear it. Examine logs first and restart only if you have to.

Q: How do I find out what an unknown error code means?

A: The best place to look is Oracle's official Database Error Messages documentation. On Linux and UNIX, you can also get a quick description by doing oerr ora 12154.

Q: How do I know whether an error is a bug?

A: These are known bugs and known post patching issues that show up as internal errors (ORA-00600, ORA-07445). Search My Oracle Support for the error arguments and your version.

Q: Is a paid license required to work with AWR?

A: AWR is included with the Diagnostics Pack which must be licensed separately on Enterprise Edition. Statspack is available for free.

Q: How frequently should I review the alert log?

A: Daily is a good practice for production system and it should have automated observation on the daily execution plans looking for the new ORA- error.

Conclusion

"Connection errors are generally a result of names, listeners, or firewalls. Space errors are a result of growth that went unnoticed. And internal errors are a result of problem solving with the right evidence, which may require consulting Oracle Support.

No documentation can list all cases; Oracle is big, and every environment has its oddities. But 80% of errors can be solved with a systematic approach, a respect for backups, and a minimum of documentation. If you haven't found your alert log yet, start there today before you need to.

I promise it'll thank you.

Ready to Create Better Content?

Join thousands of content creators who use SpeedContent to generate high-quality, SEO-optimized articles that rank.