Frequently Encountered Problems When Working With Oracle Databases and How to Solve Them

|

Oracle databases are among the most powerful and feature-rich relational database systems available. Their robust capabilities make them ideal for enterprise-level applications, yet developers and DBAs often encounter a range of challenges that can be difficult to debug or resolve efficiently. This article explores common Oracle database issues and provides concrete solutions and SQL/PLSQL coding examples to tackle them.

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

Problem:

This is a common error that occurs when a connection string to the Oracle database is incorrect or cannot be resolved using the provided TNS configuration.

Cause:

  • The TNS entry is missing in the tnsnames.ora file.

  • There is a typo in the alias name.

  • Oracle client is not correctly installed or configured.

Solution:

Check the tnsnames.ora file for the correct connection identifier.

ini
# Sample tnsnames.ora entry
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myservice)
)
)

Then connect using:

bash
sqlplus user/password@MYDB

Ensure the environment variable TNS_ADMIN points to the correct location of the tnsnames.ora file.

ORA-00001: Unique Constraint Violated

Problem:

This error occurs when attempting to insert a duplicate value in a column that has a UNIQUE or PRIMARY KEY constraint.

Example:

sql
INSERT INTO employees (id, name) VALUES (1, 'John Doe');

If ID 1 already exists, this error will occur.

Solution:

Check if the record already exists before inserting.

sql
MERGE INTO employees e
USING (SELECT 1 AS id, 'John Doe' AS name FROM dual) s
ON (e.id = s.id)
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (s.id, s.name);

Alternatively, handle the error in PL/SQL:

plsql
BEGIN
INSERT INTO employees (id, name) VALUES (1, 'John Doe');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Duplicate entry found.');
END;

ORA-01555: Snapshot Too Old

Problem:

This error occurs during long-running queries or transactions when Oracle tries to access an older version of data that has been overwritten in the undo tablespace.

Solution:

  • Increase undo retention time:

sql
ALTER SYSTEM SET UNDO_RETENTION = 1800;

  • Ensure your undo tablespace is large enough:

sql
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';

  • Avoid fetching too many rows in one go in PL/SQL loops:

plsql
-- Inefficient
FOR rec IN (SELECT * FROM huge_table) LOOP
-- process
END LOOP;— Better: Use BULK COLLECT with LIMIT
DECLARE
TYPE t_rec IS TABLE OF huge_table%ROWTYPE;
l_data t_rec;
BEGIN
LOOP
FETCH c_bulk COLLECT INTO l_data LIMIT 1000;
EXIT WHEN l_data.COUNT = 0;FOR i IN 1 .. l_data.COUNT LOOP
— process
END LOOP;
END LOOP;
END;

ORA-00904: Invalid Identifier

Problem:

This error occurs when referencing a column name that does not exist or is misspelled.

Solution:

Double-check the column name, especially if it’s case-sensitive or includes reserved keywords.

sql
SELECT "FirstName" FROM employees;

The above query requires that the column was created with double quotes:

sql
CREATE TABLE employees (
"FirstName" VARCHAR2(50)
);

Avoid using reserved keywords or enforce naming conventions to prevent issues.

ORA-03113: End-of-file on Communication Channel

Problem:

This error typically arises due to a database crash, a firewall timeout, or network disconnections.

Solution:

  • Check Oracle alert logs for crash or background errors.

  • Ensure firewall and TCP keepalive settings are not killing long idle sessions.

  • Increase SQL*Net SQLNET.EXPIRE_TIME to detect dead connections:

ini
# sqlnet.ora
SQLNET.EXPIRE_TIME=10

Locking and Deadlock Issues

Problem:

Two or more sessions are blocking each other, or a session is blocked due to uncommitted changes in another session.

Detection:

sql
SELECT
l1.sid || ',' || l2.sid AS deadlock,
l1.id1, l1.id2, l1.lmode, l2.lmode
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;

Resolution:

  • Identify and kill the blocking session:

sql

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

— Kill session
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

  • Commit or rollback long uncommitted transactions to release locks.

Poor SQL Performance Due to Missing Indexes

Problem:

A query performs full table scans, causing performance issues.

Diagnosis:

sql
EXPLAIN PLAN FOR SELECT * FROM employees WHERE email = 'john@example.com';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Solution:

Add an index on the column:

sql
CREATE INDEX idx_email ON employees(email);

Problems with Data Type Mismatches

Problem:

Implicit conversions during comparisons can result in full scans or unexpected behavior.

Example:

sql
SELECT * FROM employees WHERE employee_id = '123';

This causes a full table scan if employee_id is a number.

Solution:

Use correct data types in WHERE clauses:

sql
SELECT * FROM employees WHERE employee_id = 123;

Incorrect NLS Settings and Character Set Issues

Problem:

Mismatch in NLS settings (like date or number formats) between the client and the database can cause data corruption or errors.

Example:

sql
-- Invalid month error
INSERT INTO orders (order_date) VALUES (TO_DATE('13-APR-2025', 'DD-MON-YYYY'));

If NLS_DATE_LANGUAGE or NLS_DATE_FORMAT is inconsistent, this fails.

Solution:

Always use explicit date formats and avoid relying on implicit conversions:

sql
TO_DATE('2025-04-13', 'YYYY-MM-DD')

Also, verify NLS settings:

sql
SELECT * FROM NLS_SESSION_PARAMETERS;

Export/Import Failures with Data Pump

Problem:

Data Pump (expdp/impdp) jobs fail due to directory access errors or lack of privileges.

Solution:

sql
-- Create directory
CREATE OR REPLACE DIRECTORY datapump_dir AS '/u01/dumps';
GRANT READ, WRITE ON DIRECTORY datapump_dir TO my_user;— Export
expdp my_user/my_password DIRECTORY=datapump_dir DUMPFILE=backup.dmp LOGFILE=backup.log— Import
impdp my_user/my_password DIRECTORY=datapump_dir DUMPFILE=backup.dmp LOGFILE=import.log

Ensure the OS directory exists and is writable by the Oracle process.

Conclusion

Working with Oracle databases is both rewarding and complex. Their vast feature set comes with a learning curve that often introduces developers and administrators to a series of common pitfalls. In this article, we explored some of the most frequently encountered Oracle database problems, such as connection issues, data consistency errors, locking problems, performance pitfalls, and configuration mismatches.

By understanding these issues deeply and using practical SQL and PL/SQL techniques, you can:

  • Prevent avoidable runtime exceptions.

  • Improve the performance and maintainability of your applications.

  • Increase system availability and reliability.

  • Strengthen your database security posture.

Whether you’re a DBA, backend engineer, or full-stack developer working with Oracle, being equipped with these solutions enhances your capability to manage and debug Oracle-based applications efficiently. Proactive monitoring, consistent use of best practices (e.g., explicit data type handling, indexed queries, robust transaction control), and periodic health checks go a long way toward minimizing downtime and ensuring long-term database stability.