ORA-02069: Global Names Must Be TRUE - Fix DB Link Naming

ORA-02069: Global_names Parameter Must Be Set to TRUE

Error Overview

Error Text: ORA-02069: global_names parameter must be set to TRUE for this operation

The ORA-02069 error is raised when an operation requires that the GLOBAL_NAMES initialization parameter be set to TRUE, but it is currently FALSE on the local database. When GLOBAL_NAMES = TRUE, Oracle enforces that every database link name must exactly match the global name of the remote database (in the form DB_NAME.DB_DOMAIN). The parameter exists to prevent naming ambiguities in complex distributed environments where many databases are interconnected.

This error typically surfaces when an administrator or developer creates a database link with an arbitrary short name (e.g., PROD) while the database enforces global naming, or when a query references a link whose name does not match the remote database’s registered global name.

Common Causes

1. Database Link Name Does Not Match Remote Global Name

A link was created as PROD but the remote database’s GLOBAL_NAME is PROD.EXAMPLE.COM
The link name omits the domain suffix required by GLOBAL_NAMES = TRUE
The link was created on a database where GLOBAL_NAMES = FALSE and later the parameter was changed to TRUE

2. GLOBAL_NAMES Parameter Changed After Links Were Created

A DBA changed GLOBAL_NAMES from FALSE to TRUE to enforce naming standards
Existing database links created with short names immediately became non-compliant
Applications referencing these links now receive ORA-02069

3. DB_DOMAIN Not Set Consistently Across Databases

The local database has DB_DOMAIN = example.com but the remote database has DB_DOMAIN = corp.example.com
The global name mismatch makes it impossible to name the link correctly on the first attempt
Domain inconsistencies across environments (DEV, UAT, PROD) cause the same link name to fail in some environments

4. Copied or Cloned Database with Changed GLOBAL_NAME

A database was cloned and the GLOBAL_NAME was changed post-clone via ALTER DATABASE RENAME GLOBAL_NAME TO
Existing links pointing to the old name still use the pre-clone name
Applications experience ORA-02069 immediately after the rename

5. Heterogeneous or Non-Oracle Links Not Matching Convention

A database link to a non-Oracle database (via Oracle Gateway) was named informally
The link name has no domain component but GLOBAL_NAMES = TRUE requires it
The remote non-Oracle system has no concept of global naming, creating a mismatch

6. Session-Level vs System-Level Parameter Mismatch

A developer session altered GLOBAL_NAMES at the session level to FALSE to test a link, but application sessions use the system default of TRUE
Code that works interactively fails in production application sessions
ALTER SESSION SET global_names = FALSE masks the underlying naming problem

Diagnostic Queries

Check Current GLOBAL_NAMES Parameter Value

-- System-level value
SELECT name, value, description
FROM v$parameter
WHERE name = 'global_names';

-- Check if the parameter can be changed dynamically
SELECT name, issys_modifiable, isses_modifiable
FROM v$parameter
WHERE name = 'global_names';

Identify the Global Name of the Local Database

-- View the local database's global name
SELECT * FROM global_name;

-- Verify DB_NAME and DB_DOMAIN
SELECT name, value
FROM v$parameter
WHERE name IN ('db_name', 'db_domain', 'global_names');

Query Global Names of Remote Databases Through Existing Links

-- Check the global name of a remote database through a database link
SELECT * FROM global_name@remote_db_link;

Review All Database Links and Their Names

-- List all database links — compare link name against expected global name
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
ORDER BY owner, db_link;

Identify Mismatched Links (Links Without Domain Component)

-- Find links that likely lack the domain suffix
SELECT
  owner,
  db_link,
  host,
  created,
  CASE
    WHEN INSTR(db_link, '.') = 0 THEN 'MISSING DOMAIN - may fail if GLOBAL_NAMES=TRUE'
    ELSE 'Has domain component'
  END AS naming_status
FROM dba_db_links
ORDER BY naming_status DESC, owner, db_link;

Check GLOBAL_NAMES at the Session Level

-- View current session's global_names setting
SELECT value
FROM v$parameter
WHERE name = 'global_names';

-- Also check via NLS/session view
SHOW PARAMETER global_names;

Verify Remote Database Global Name Before Creating a Link

-- Method 1: If a temporary link already exists, query it
SELECT db_link_name FROM global_name@temp_link;

-- Method 2: Ask the remote DBA to run this on the remote database
SELECT * FROM global_name;

Step-by-Step Resolution

1. Determine the Correct Remote Global Name

Before creating or renaming any database link, confirm the exact global name of the remote database:

-- If you already have any link to the remote DB, query its global name
SELECT * FROM global_name@existing_link;

-- Expected result example: PROD.EXAMPLE.COM

If you do not have any link yet, ask the remote DBA to run SELECT * FROM global_name; and provide the output.

2. Option A — Set GLOBAL_NAMES = FALSE (Simplest, Less Strict)

If your organization does not require enforced global naming, set the parameter to FALSE. This allows database links to use any convenient name regardless of the remote database’s global name:

-- Disable global name enforcement at the system level
ALTER SYSTEM SET global_names = FALSE SCOPE=BOTH;

-- Verify the change
SHOW PARAMETER global_names;

This is the fastest fix but reduces naming consistency in large distributed environments.

3. Option B — Recreate the Database Link with the Correct Global Name

When GLOBAL_NAMES = TRUE must remain enforced, the database link name must exactly match the remote database’s global name:

-- Step 1: Find the remote database's global name
-- (Ask remote DBA or query via a temporary connection)
-- Result: PROD.EXAMPLE.COM

-- Step 2: Drop the incorrectly named link
DROP DATABASE LINK prod;          -- old short name
DROP PUBLIC DATABASE LINK prod;   -- if it is a public link

-- Step 3: Recreate with the correct full global name
CREATE DATABASE LINK "PROD.EXAMPLE.COM"
  CONNECT TO remote_user IDENTIFIED BY remote_password
  USING 'prod_service';

-- Step 4: Test the new link
SELECT SYSDATE FROM dual@"PROD.EXAMPLE.COM";

4. Option C — Rename the Remote Database’s Global Name

In some cases, the remote database’s global name can be changed to a simpler name that matches existing link names. Use this only when the remote DBA agrees and there are no conflicting naming requirements:

-- On the REMOTE database (must have DBA privileges):
ALTER DATABASE RENAME GLOBAL_NAME TO newname.example.com;

-- Verify the change on the remote database
SELECT * FROM global_name;

Note: Renaming a global database name invalidates all existing links TO that database from other systems. Coordinate carefully.

5. Option D — Session-Level Workaround for Development or Transition

During a migration or while links are being recreated, you can temporarily disable GLOBAL_NAMES enforcement at the session level:

-- For the current session only — does not affect other sessions
ALTER SESSION SET global_names = FALSE;

-- Now run the query that was failing
SELECT * FROM some_table@prod;

-- Re-enable for the session if desired
ALTER SESSION SET global_names = TRUE;

This is not a permanent solution and should not be used in production application code.

6. Update Application References to Use the Correct Link Name

After recreating the link with the global name, update all application code, SQL scripts, stored procedures, and synonyms that reference the old link name:

-- Find stored code referencing the old link name
SELECT
  owner,
  name,
  type,
  SUBSTR(text, 1, 200) AS code_snippet
FROM dba_source
WHERE UPPER(text) LIKE '%@PROD%'  -- old link name
  AND type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'TRIGGER', 'VIEW')
ORDER BY owner, name;

-- Find synonyms pointing to the old link
SELECT owner, synonym_name, db_link
FROM dba_synonyms
WHERE db_link = 'PROD'
ORDER BY owner, synonym_name;

Update each synonym to point to the new link name:

-- Drop and recreate synonyms with the new link name
DROP SYNONYM local_remote_table;
CREATE SYNONYM local_remote_table FOR remote_table@"PROD.EXAMPLE.COM";

Prevention Strategies

1. Standardize DB_NAME and DB_DOMAIN Across All Databases

Establish and enforce a naming convention across all Oracle databases in your organization before enabling GLOBAL_NAMES = TRUE:

-- Verify naming consistency (run on each database in the environment)
SELECT
  SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name,
  SYS_CONTEXT('USERENV', 'DB_DOMAIN') AS db_domain,
  (SELECT name FROM global_name) AS global_name
FROM dual;

2. Always Check the Remote Global Name Before Creating a Link

Make it a standard procedure to query the remote database’s global name before creating any new database link:

-- Standard link creation checklist procedure
-- Step 1: Get remote global name (from remote DBA or existing connection)
-- Step 2: Confirm GLOBAL_NAMES parameter setting on local database
SHOW PARAMETER global_names;

-- Step 3: Create link with name matching remote global name if GLOBAL_NAMES=TRUE
CREATE DATABASE LINK "REMOTE_DB.DOMAIN.COM"
  CONNECT TO link_user IDENTIFIED BY link_password
  USING 'remote_tns_alias';

-- Step 4: Test immediately
SELECT * FROM global_name@"REMOTE_DB.DOMAIN.COM";

3. Use a Database Link Inventory Table

Maintain a local inventory of all database links, their target global names, and their current status:

CREATE TABLE db_link_inventory (
  link_owner        VARCHAR2(128),
  link_name         VARCHAR2(128),
  remote_global_name VARCHAR2(200),
  remote_host       VARCHAR2(200),
  link_username     VARCHAR2(128),
  created_date      DATE,
  last_tested       DATE,
  status            VARCHAR2(20),
  notes             VARCHAR2(500),
  CONSTRAINT pk_db_link_inv PRIMARY KEY (link_owner, link_name)
);

4. Test All Links After Enabling GLOBAL_NAMES = TRUE

Before enabling GLOBAL_NAMES = TRUE in production, test every existing link:

-- Generate test statements for all database links
SELECT
  'SELECT SYSDATE FROM dual@' ||
  CASE WHEN owner = 'PUBLIC' THEN '' ELSE '"' || owner || '".' END ||
  '"' || db_link || '";' AS test_sql,
  owner,
  db_link,
  host
FROM dba_db_links
ORDER BY owner, db_link;

5. Standardize Link Creation with a DBA Utility Procedure

CREATE OR REPLACE PROCEDURE create_db_link_safe(
  p_link_name     IN VARCHAR2,
  p_remote_user   IN VARCHAR2,
  p_remote_pass   IN VARCHAR2,
  p_tns_alias     IN VARCHAR2,
  p_is_public     IN BOOLEAN DEFAULT FALSE
) AS
  v_global_names  VARCHAR2(10);
  v_link_sql      VARCHAR2(1000);
BEGIN
  -- Check GLOBAL_NAMES setting
  SELECT value INTO v_global_names
  FROM v$parameter WHERE name = 'global_names';

  IF UPPER(v_global_names) = 'TRUE' THEN
    DBMS_OUTPUT.PUT_LINE(
      'WARNING: GLOBAL_NAMES=TRUE. Ensure link name "' || p_link_name ||
      '" exactly matches the remote database global name.'
    );
  END IF;

  -- Build and execute the CREATE DATABASE LINK statement
  v_link_sql := 'CREATE ' ||
    CASE WHEN p_is_public THEN 'PUBLIC ' ELSE '' END ||
    'DATABASE LINK "' || p_link_name || '" ' ||
    'CONNECT TO ' || p_remote_user || ' IDENTIFIED BY "' || p_remote_pass || '" ' ||
    'USING ''' || p_tns_alias || '''';

  EXECUTE IMMEDIATE v_link_sql;
  DBMS_OUTPUT.PUT_LINE('Link created: ' || p_link_name);
END;
/

Diagnostic Scripts

These Oracle Day by Day utility scripts assist with distributed configuration diagnostics:

gvsess.sql — Session diagnostics for distributed operation failures
health.sql — Database configuration health check

ORA-02055 - Distributed update failed; rollback required
ORA-02063 - Preceding line from database link
ORA-02064 - Distributed operation not supported
ORA-02067 - Transaction or savepoint rollback required
ORA-02070 - Database does not support operation in this context
ORA-02049 - Timeout: distributed transaction waiting for lock

Emergency Response

Quick Fixes

Temporarily disable GLOBAL_NAMES to restore connectivity immediately

ALTER SYSTEM SET global_names = FALSE SCOPE=MEMORY;
-- This takes effect immediately without a restart

Session-level workaround for a specific user
```
ALTER SESSION SET global_names = FALSE;
```

Recreate a specific failing link with the correct global name

DROP DATABASE LINK short_name;
CREATE DATABASE LINK "FULL.GLOBAL.NAME"
  CONNECT TO user IDENTIFIED BY password
  USING 'tns_alias';

Post-Resolution Cleanup

-- After recreating all links correctly, re-enable GLOBAL_NAMES if needed
ALTER SYSTEM SET global_names = TRUE SCOPE=BOTH;

-- Validate all links are now compliant
SELECT
  db_link,
  CASE WHEN INSTR(db_link, '.') > 0 THEN 'COMPLIANT' ELSE 'NEEDS ATTENTION' END AS status
FROM dba_db_links
ORDER BY status DESC, db_link;

-- Test each compliant link
SELECT SYSDATE FROM dual@"FULL.GLOBAL.NAME";

-- Update any synonyms or stored code referencing the old link names
-- (see resolution step 6 above for the search query)