Oracle ORA-02289 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02289: sequence does not exist — Causes, Fixes & Prevention

ORA-02289 is thrown by Oracle Database when a session attempts to reference a sequence object that doesn't exist in the accessible schema. This can happen during NEXTVAL/CURRVAL calls, or when executing DDL statements like DROP SEQUENCE or ALTER SEQUENCE against a non-existent sequence. It's one of the most common deployment-related errors in Oracle environments.

Top 3 Causes

1. The Sequence Simply Doesn't Exist (Never Created or Dropped)

The most frequent cause — the sequence was never deployed to the target environment, or it was accidentally dropped.

-- Check if the sequence exists in your schema
SELECT SEQUENCE_NAME, LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'ORDER_SEQ';

-- Check across all schemas (requires DBA privilege)
SELECT SEQUENCE_OWNER, SEQUENCE_NAME
FROM DBA_SEQUENCES
WHERE SEQUENCE_NAME = 'ORDER_SEQ';

-- If missing, recreate it safely
DECLARE
    v_start NUMBER;
BEGIN
    SELECT NVL(MAX(ORDER_ID), 0) + 1 INTO v_start FROM ORDERS;
    EXECUTE IMMEDIATE
        'CREATE SEQUENCE ORDER_SEQ START WITH ' || v_start ||
        ' INCREMENT BY 1 NOCYCLE NOCACHE';
END;
/

2. Wrong Schema — Missing Privileges or Synonym

Oracle resolves object names based on the current session's schema. If ORDER_SEQ belongs to SCOTT but you're connected as APP_USER without a synonym or explicit schema prefix, ORA-02289 will fire.

-- Option A: Use fully qualified schema.sequence reference
SELECT SCOTT.ORDER_SEQ.NEXTVAL FROM DUAL;

-- Option B: Grant privilege and create a private synonym
GRANT SELECT ON SCOTT.ORDER_SEQ TO APP_USER;

CREATE SYNONYM ORDER_SEQ FOR SCOTT.ORDER_SEQ;

-- Now this works without schema prefix
SELECT ORDER_SEQ.NEXTVAL FROM DUAL;

3. Case-Sensitive Name or Typo

If a sequence was created with double quotes, Oracle stores it with exact case. Calling it without quotes causes ORA-02289 because Oracle uppercases unquoted identifiers by default.

-- Sequence created with double quotes (case-sensitive)
CREATE SEQUENCE "mySeq" START WITH 1 INCREMENT BY 1;

-- WRONG — Oracle looks for MYSEQ, not mySeq
SELECT mySeq.NEXTVAL FROM DUAL;  -- ORA-02289

-- CORRECT — must use double quotes
SELECT "mySeq".NEXTVAL FROM DUAL;

-- Best practice: avoid quoted identifiers entirely
DROP SEQUENCE "mySeq";
CREATE SEQUENCE MYSEQ START WITH 1 INCREMENT BY 1;
SELECT MYSEQ.NEXTVAL FROM DUAL;

Quick Fix Checklist

-- 1. Verify the exact sequence name (watch for typos)
SELECT OBJECT_NAME, STATUS
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE'
  AND OBJECT_NAME LIKE '%SEQ%';

-- 2. Confirm current user and schema context
SELECT USER, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

-- 3. List all sequences visible to current user
SELECT SEQUENCE_NAME FROM USER_SEQUENCES ORDER BY 1;

Prevention Tips

Automate pre-deployment validation. Add a sequence existence check to your CI/CD pipeline before any release. A simple script that queries USER_SEQUENCES or DBA_SEQUENCES for all required objects can catch missing sequences before they hit production.

-- Pre-deployment validation query
SELECT 'ORDER_SEQ' AS SEQ, COUNT(*) AS FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME='ORDER_SEQ'
UNION ALL
SELECT 'CUSTOMER_SEQ', COUNT(*) FROM USER_SEQUENCES WHERE SEQUENCE_NAME='CUSTOMER_SEQ';
-- Any row with FOUND=0 needs immediate attention before deployment.

Standardize naming conventions and version-control all DDL. Always use uppercase, unquoted sequence names following a consistent pattern like TABLENAME_SEQ. Store all DDL scripts in Git or a similar version control system so any environment can be rebuilt reliably. This eliminates the "it exists in dev but not in prod" class of errors permanently.

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

DEV Community