ORA-02011: Duplicate Database Link Name - Drop and Recreate

ORA-02011: Duplicate Database Link Name

Error Overview

Error Text: ORA-02011: duplicate database link name

ORA-02011 is raised when a user attempts to create a database link with a name that already exists in their schema or as a public link in the database. Database link names must be unique within a given scope (private to user, or public to all users).

Common Causes

Duplicate in Same Scope

• A private database link with the same name already exists for the user
• A public database link with the same name already exists
• Attempted to create public link with same name as existing private (with GLOBAL_NAMES=FALSE)

Recreating After Migration

• Database link wasn’t dropped before recreation
• Cloned schema retained existing links
• Refresh script doesn’t drop before create

Case Sensitivity Confusion

• Link created with quotes (case sensitive) collides with unquoted name
• Mixed case in CREATE DATABASE LINK statements

Diagnostic Steps

Identify Existing Database Link

-- Private links (current user)
SELECT db_link, username, host, created
FROM user_db_links
WHERE db_link = 'TARGET_DB';

-- All private links (DBA view)
SELECT owner, db_link, username, host, created
FROM dba_db_links
WHERE db_link = 'TARGET_DB';

-- Public links
SELECT db_link, username, host
FROM dba_db_links
WHERE owner = 'PUBLIC'
AND db_link = 'TARGET_DB';

-- Check all variants (case sensitive search)
SELECT owner, db_link, host
FROM dba_db_links
WHERE UPPER(db_link) = 'TARGET_DB';

Verify GLOBAL_NAMES Setting

SHOW PARAMETER global_names;

-- If TRUE, link name must match remote DB global name
SELECT * FROM global_name;

Resolution Steps

1. Drop and Recreate

-- Drop private link
DROP DATABASE LINK target_db;

-- Drop public link (requires DBA privilege)
DROP PUBLIC DATABASE LINK target_db;

-- Recreate with new definition
CREATE DATABASE LINK target_db
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'TARGET_TNS';

-- For public link
CREATE PUBLIC DATABASE LINK target_db
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'TARGET_TNS';

2. Use Different Name

-- If duplicate is intentional (different remote DB)
CREATE DATABASE LINK target_db_v2
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'TARGET_TNS_V2';

3. Cross-Schema Conflicts

When public link conflicts with intended private link, drop or rename:

-- As DBA, identify owner
SELECT owner, db_link, username
FROM dba_db_links
WHERE UPPER(db_link) = 'TARGET_DB';

-- Drop public if it shouldn't be public
DROP PUBLIC DATABASE LINK target_db;

-- Then create as private
CREATE DATABASE LINK target_db
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'TARGET_TNS';

4. Idempotent Create Script

-- Drop if exists, then create
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM user_db_links
    WHERE db_link = 'TARGET_DB';

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'DROP DATABASE LINK target_db';
    END IF;
END;
/

CREATE DATABASE LINK target_db
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'TARGET_TNS';

5. Handle Case Sensitivity

-- Avoid creating both quoted and unquoted versions
CREATE DATABASE LINK target_db ...;     -- Stored as TARGET_DB
CREATE DATABASE LINK "target_db" ...;   -- Stored as target_db (different)

-- Always use one convention; uppercase unquoted is recommended

Sample Output

SQL> CREATE DATABASE LINK reporting
  2  CONNECT TO rpt_user IDENTIFIED BY rpt_pass
  3  USING 'RPTDB';
CREATE DATABASE LINK reporting
*
ERROR at line 1:
ORA-02011: duplicate database link name

SQL> SELECT owner, db_link, username, created
  2  FROM dba_db_links
  3  WHERE UPPER(db_link) LIKE 'REPORTING%';

OWNER      DB_LINK         USERNAME    CREATED
---------- --------------- ----------- ---------
APP_USER   REPORTING       RPT_USER    01-MAR-26
PUBLIC     REPORTING       RPT_USER    15-FEB-25

-- Public link exists; drop it first
SQL> DROP PUBLIC DATABASE LINK reporting;
Database link dropped.

SQL> CREATE DATABASE LINK reporting CONNECT TO rpt_user IDENTIFIED BY rpt_pass USING 'RPTDB';
Database link created.

Common Scenarios

Scenario 1: Refresh Script Failure

DBA cloning schema for monthly refresh, link already exists.

Fix: Always drop before create in idempotent scripts.

Scenario 2: GLOBAL_NAMES Conflict

GLOBAL_NAMES=TRUE; trying to create link with name not matching remote DB global name.

Fix: Either set GLOBAL_NAMES=FALSE or use the remote DB’s exact global_name (SELECT * FROM global_name@remote;).

Scenario 3: Public vs Private

CREATE DATABASE LINK reporting ...
ORA-02011: duplicate database link name

Fix: Public link with same name exists. Either use the public link directly or drop it (DBA privilege required).

Prevention Strategies

Naming Conventions

• Prefix links with environment (PROD_RPT, DEV_RPT)
• Include purpose in name (SALES_HUB_LINK)
• Avoid generic names like LINK1, DBLINK
• Document all links in a registry

Idempotent Scripts

-- Reusable drop-and-create wrapper
CREATE OR REPLACE PROCEDURE recreate_dblink(
    p_link_name IN VARCHAR2,
    p_user      IN VARCHAR2,
    p_pwd       IN VARCHAR2,
    p_tns       IN VARCHAR2,
    p_public    IN BOOLEAN DEFAULT FALSE
) AS
    v_count NUMBER;
    v_drop  VARCHAR2(200);
    v_create VARCHAR2(500);
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM dba_db_links
    WHERE db_link = UPPER(p_link_name)
    AND owner = CASE WHEN p_public THEN 'PUBLIC' ELSE USER END;

    IF v_count > 0 THEN
        v_drop := 'DROP ' || CASE WHEN p_public THEN 'PUBLIC ' END
                  || 'DATABASE LINK ' || p_link_name;
        EXECUTE IMMEDIATE v_drop;
    END IF;

    v_create := 'CREATE ' || CASE WHEN p_public THEN 'PUBLIC ' END
                || 'DATABASE LINK ' || p_link_name
                || ' CONNECT TO ' || p_user
                || ' IDENTIFIED BY "' || p_pwd || '"'
                || ' USING ''' || p_tns || '''';
    EXECUTE IMMEDIATE v_create;
END;
/

Centralized Link Registry

-- Maintain documentation table
CREATE TABLE dblink_registry (
    link_name      VARCHAR2(128) PRIMARY KEY,
    owner          VARCHAR2(128),
    purpose        VARCHAR2(500),
    target_db      VARCHAR2(128),
    last_validated DATE
);

Related Errors

• ORA-02019: Connection description for remote database not found
• ORA-02020: Too many database links in use
• ORA-02063: Preceding line from database link
• ORA-02085: Database link connects to a different database
• ORA-12154: TNS could not resolve connect identifier

Troubleshooting Checklist

• Check dba_db_links for existing link with same name
• Verify whether duplicate is private or public
• Drop existing link before recreate
• Avoid case-mixing (use uppercase unquoted convention)
• Use idempotent scripts with conditional drop
• Document all links in a registry