Oracle ORA-04004 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04004: MINVALUE must be less than MAXVALUE

ORA-04004 is an Oracle error thrown when creating or altering a sequence where the MINVALUE is set to a value greater than or equal to MAXVALUE. Oracle sequences require a valid numeric range to operate, and the rule MINVALUE < MAXVALUE must always hold true. This error can occur during both CREATE SEQUENCE and ALTER SEQUENCE operations.

Top 3 Causes

1. Swapped or Equal MINVALUE / MAXVALUE on CREATE SEQUENCE

The most common cause is simply entering the values in the wrong order or using identical values for both parameters.

-- ERROR: MINVALUE > MAXVALUE
CREATE SEQUENCE seq_orders
    START WITH 1
    INCREMENT BY 1
    MINVALUE 9999   -- This is larger than MAXVALUE!
    MAXVALUE 100
    NOCYCLE;

-- CORRECT
CREATE SEQUENCE seq_orders
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999999
    NOCYCLE
    CACHE 20;

2. Misunderstanding Descending Sequences

When creating a descending sequence (negative INCREMENT BY), many DBAs mistakenly think MINVALUE should be larger than MAXVALUE. This is wrong — the MINVALUE < MAXVALUE rule always applies regardless of sequence direction.

-- ERROR: Equal values
CREATE SEQUENCE seq_countdown
    START WITH 100
    INCREMENT BY -1
    MINVALUE 100    -- Same as MAXVALUE!
    MAXVALUE 100
    NOCYCLE;

-- CORRECT: Descending sequence
CREATE SEQUENCE seq_countdown
    START WITH 100
    INCREMENT BY -1
    MINVALUE 1      -- Still MINVALUE < MAXVALUE
    MAXVALUE 100
    NOCYCLE
    NOCACHE;

-- Verify it works
SELECT seq_countdown.NEXTVAL FROM DUAL; -- 100
SELECT seq_countdown.NEXTVAL FROM DUAL; -- 99

3. Invalid ALTER SEQUENCE Without Checking Current State

Attempting to modify an existing sequence without first checking its current MIN/MAX values can lead to invalid configurations.

-- Check current sequence state first
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, LAST_NUMBER
FROM   USER_SEQUENCES
WHERE  SEQUENCE_NAME = 'SEQ_ORDERS';

-- ERROR: MINVALUE exceeds MAXVALUE after alter
ALTER SEQUENCE seq_orders
    MINVALUE 50000
    MAXVALUE 1000;  -- Invalid!

-- CORRECT ALTER
ALTER SEQUENCE seq_orders
    MINVALUE 1
    MAXVALUE 99999999;

-- Oracle 12c+: Restart a sequence cleanly
ALTER SEQUENCE seq_orders RESTART START WITH 1;

Quick Fix Solutions

If you encounter ORA-04004, apply the following steps immediately:

-- Step 1: Query the current sequence definition
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,
       INCREMENT_BY, LAST_NUMBER, CYCLE_FLAG
FROM   USER_SEQUENCES
WHERE  SEQUENCE_NAME = 'YOUR_SEQUENCE_NAME';

-- Step 2: Fix with a valid ALTER SEQUENCE
ALTER SEQUENCE your_sequence_name
    MINVALUE 1
    MAXVALUE 9999999999;

-- Step 3: If the sequence needs to be fully reset (Oracle 11g and below)
DROP SEQUENCE your_sequence_name;
CREATE SEQUENCE your_sequence_name
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999999999
    NOCYCLE
    CACHE 20;

Prevention Tips

Always validate MINVALUE < MAXVALUE before executing DDL. Add a pre-deployment validation script to your CI/CD pipeline or change management process to catch this mistake before it hits production.
Use a standard sequence template. Establish an organizational DDL standard for sequences with safe default values, and enforce peer review for any sequence-related changes. Monitor sequence usage regularly with the query below to proactively extend ranges before they run out.

-- Monitor sequence usage across all sequences
SELECT SEQUENCE_NAME,
       MIN_VALUE,
       MAX_VALUE,
       LAST_NUMBER,
       MAX_VALUE - LAST_NUMBER        AS REMAINING_VALUES,
       ROUND((LAST_NUMBER - MIN_VALUE)
           / NULLIF(MAX_VALUE - MIN_VALUE, 0) * 100, 2) AS USED_PCT
FROM   USER_SEQUENCES
ORDER BY USED_PCT DESC NULLS LAST;

Related Errors

ORA-04006 – START WITH value out of MINVALUE/MAXVALUE range
ORA-04007 – MINVALUE cannot be made larger than current sequence value
ORA-04008 – MAXVALUE cannot be made smaller than current sequence value
ORA-02289 – Sequence does not exist

📖 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