DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02021 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02021: DDL Operations Are Not Allowed on a Remote Database

ORA-02021 is thrown by Oracle when a session attempts to execute a DDL (Data Definition Language) statement — such as CREATE, ALTER, or DROP — against a remote database through a Database Link (DB Link). Oracle's distributed database architecture intentionally restricts DDL operations over DB Links to preserve transactional integrity and security across distributed environments. To perform DDL on a remote database, you must connect directly to that database instance.

Top 3 Causes

1. Direct DDL Execution via DB Link

The most common cause is attempting to run DDL statements directly using the @dblink syntax in a local session.

-- This will trigger ORA-02021
CREATE TABLE orders_archive@remote_db_link (
    order_id   NUMBER PRIMARY KEY,
    order_date DATE,
    amount     NUMBER(10,2)
);

-- Also triggers ORA-02021
ALTER TABLE employees@remote_db_link ADD (phone_number VARCHAR2(20));

-- Correct approach: connect directly to the remote DB and run
CREATE TABLE orders_archive (
    order_id   NUMBER PRIMARY KEY,
    order_date DATE,
    amount     NUMBER(10,2)
);
Enter fullscreen mode Exit fullscreen mode

2. Dynamic DDL Inside PL/SQL Using EXECUTE IMMEDIATE over DB Link

Developers sometimes attempt to execute dynamic DDL inside a PL/SQL block targeting a remote database via EXECUTE IMMEDIATE, which Oracle does not permit.

-- This will FAIL with ORA-02021
DECLARE
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'CREATE TABLE temp_log (log_id NUMBER, log_msg VARCHAR2(200))';
    EXECUTE IMMEDIATE v_sql || '@remote_db_link';  -- Not allowed
END;
/

-- Workaround: Create a procedure on the remote DB first
-- [On the remote database]
CREATE OR REPLACE PROCEDURE exec_remote_ddl (p_sql IN VARCHAR2) AS
BEGIN
    EXECUTE IMMEDIATE p_sql;
END;
/

-- [From the local database, call via DB Link]
BEGIN
    exec_remote_ddl@remote_db_link(
        'CREATE TABLE temp_log (log_id NUMBER, log_msg VARCHAR2(200))'
    );
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Schema Synchronization Scripts Using DB Links

Automated deployment scripts or schema sync tools that loop through multiple databases using DB Links and attempt to apply DDL changes across all of them will fail with this error.

-- Wrong approach in a sync script
BEGIN
    FOR rec IN (SELECT db_link FROM sync_targets) LOOP
        -- This pattern will cause ORA-02021
        EXECUTE IMMEDIATE
            'CREATE INDEX idx_emp_dept ON employees@' || rec.db_link ||
            '(department_id)';
    END LOOP;
END;
/

-- Correct approach: Use CTAS locally to pull remote data instead
CREATE TABLE local_employees_snapshot AS
SELECT emp_id, emp_name, department_id, salary
FROM employees@remote_db_link;

-- Or insert remote data into an existing local table
INSERT INTO staging_employees
SELECT * FROM employees@remote_db_link WHERE hire_date > SYSDATE - 30;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Connect directly to the remote database and execute the DDL there — this is always the cleanest solution.
  • Use a remote stored procedure that wraps EXECUTE IMMEDIATE, then invoke it via DB Link from your local session.
  • Redesign your architecture so that DDL scripts are deployed independently to each target database rather than being pushed through DB Links. 
-- Verify which database you are currently connected to before running DDL
SELECT SYS_CONTEXT('USERENV', 'DB_NAME')      AS db_name,
       SYS_CONTEXT('USERENV', 'SERVER_HOST')   AS host,
       SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS instance
FROM dual;

-- Test DB Link connectivity before use
SELECT * FROM dual@remote_db_link;

-- List all available DB Links in the current schema
SELECT db_link, username, host
FROM user_db_links
ORDER BY db_link;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enforce a pre-DDL validation step in all deployment scripts that confirms the current session is connected to the intended database using SYS_CONTEXT. Incorporate this check into code review guidelines so that any @dblink reference in a DDL statement is caught before it reaches production.

  2. Adopt a dedicated schema migration tool such as Flyway or Liquibase, and design your CI/CD pipeline to connect independently to each target database to apply DDL changes. This eliminates the temptation to use DB Links for DDL propagation and ensures each database is managed with a clean, auditable change history.

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