DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02024 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02024: Database Link Not Found — Causes, Fixes & Prevention

ORA-02024 is thrown by Oracle when a query or DDL statement references a database link that does not exist in the current user's schema or as a PUBLIC link. This commonly happens when a link has been dropped, was never created in the target environment, or is referenced with an incorrect name.

Top 3 Causes

1. The Database Link Simply Does Not Exist

The link was never created, or it was dropped before the referencing code was updated.

-- Check which links are available to your session
SELECT db_link, owner, host, created
FROM all_db_links
ORDER BY owner, db_link;

-- Also check public links specifically
SELECT db_link, username, host
FROM dba_db_links
WHERE owner = 'PUBLIC';
Enter fullscreen mode Exit fullscreen mode

Quick Fix — Create the missing link:

-- Create a private link
CREATE DATABASE LINK remote_db_link
  CONNECT TO remote_user IDENTIFIED BY "P@ssw0rd"
  USING 'REMOTE_TNS_ALIAS';

-- Verify the link works
SELECT SYSDATE FROM dual@remote_db_link;
Enter fullscreen mode Exit fullscreen mode

2. Misspelled Link Name or Case-Sensitivity Issue

When a database link is created with double quotes, Oracle treats the name as case-sensitive. Referencing it without the exact casing causes ORA-02024.

-- This creates a case-sensitive link named exactly "MyLink"
CREATE DATABASE LINK "MyLink"
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING 'REMOTE_DB';

-- WRONG: causes ORA-02024
SELECT * FROM emp@MYLINK;
SELECT * FROM emp@mylink;

-- CORRECT: must match exact case
SELECT * FROM emp@"MyLink";

-- Best practice: recreate without quotes to avoid case sensitivity
DROP DATABASE LINK "MyLink";
CREATE DATABASE LINK mylink
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING 'REMOTE_DB';
Enter fullscreen mode Exit fullscreen mode

3. PUBLIC vs. PRIVATE Link Scope Mismatch

A PRIVATE link is only accessible to the schema that created it. If another user tries to use it, Oracle cannot find it and raises ORA-02024.

-- SCOTT's private link is invisible to HR user
-- When logged in as HR, this fails if the link belongs to SCOTT:
SELECT * FROM scott_table@remote_db_link;  -- ORA-02024

-- Solution: Create a PUBLIC link (requires DBA privilege)
CREATE PUBLIC DATABASE LINK remote_db_link
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.1)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=REMOTEDB)))';

-- Now any user can reference the link
SELECT * FROM remote_table@remote_db_link;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Identify existing links
SELECT db_link, owner, username, host FROM dba_db_links ORDER BY owner;

-- Step 2: Drop and recreate if definition is wrong
DROP DATABASE LINK bad_link;
CREATE DATABASE LINK correct_link
  CONNECT TO app_user IDENTIFIED BY "SecurePass#1"
  USING 'TARGET_DB';

-- Step 3: Test connectivity immediately after creation
SELECT * FROM dual@correct_link;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Document and version-control all database links.
Store CREATE DATABASE LINK DDL scripts in your Git repository and include a "DB Link Verification" step in every deployment checklist. Run a periodic audit query to track link inventory across environments.

-- Audit query to run regularly
SELECT owner, db_link, host, created
FROM dba_db_links
ORDER BY created DESC;
Enter fullscreen mode Exit fullscreen mode

2. Handle ORA-02024 explicitly in PL/SQL code.
Wrap remote calls in exception handlers so failures are caught gracefully and alert operations teams before users are impacted.

BEGIN
  -- Attempt to use the remote link
  INSERT INTO remote_table@remote_db_link VALUES (1, 'test');
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -2024 THEN
      -- Log the error and notify operations
      INSERT INTO error_log (error_code, error_msg, log_time)
      VALUES (SQLCODE, 'Database link not found: remote_db_link', SYSDATE);
      COMMIT;
      RAISE_APPLICATION_ERROR(-20001,
        'Remote database link is unavailable. Please contact DBA.');
    ELSE
      RAISE;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-02019 Connection description for remote database not found (TNS issue)
ORA-12154 TNS alias not found in tnsnames.ora
ORA-01017 Invalid username/password on the remote database
ORA-02085 Database link name does not match global_name of remote DB

📖 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)