Scops
Docs Download

Oracle problems

ORA-08004: Sequence Exceeds MAXVALUE. Which Sequences Run Out, the Date, and the Fix

2 November 2026

A sequence runs out on a Tuesday afternoon after fifteen years, and the insert that fails is the one in the order entry screen. The limit was typed in by someone long gone, and the date it would be reached was computable all along, from the rate the sequence is used at.

What the error says

ORA-08004: sequence APP.ORDER_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated. The sequence reached its MAXVALUE and does not cycle. An identity column gives the same error with a system name: sequence ISEQ$$_71234.NEXTVAL.

Three limits look alike and are not the same error:

Which sequences are close

SELECT s.sequence_owner, s.sequence_name,
       s.last_number, s.max_value, s.increment_by,
       ROUND(s.last_number / s.max_value * 100, 2) AS pct_used,
       s.max_value - s.last_number                 AS headroom
FROM   dba_sequences s
WHERE  s.sequence_owner NOT IN ('SYS', 'SYSTEM')
AND    s.cycle_flag = 'N'
AND    s.max_value < 1e27
ORDER  BY pct_used DESC
FETCH FIRST 20 ROWS ONLY;

The filter on MAX_VALUE leaves out sequences with the default limit, which do not run out in practice. LAST_NUMBER is the start of the next cached batch, so it runs a little ahead of the last value handed out.

For identity columns, the real limit is the smaller of the sequence and the column:

SELECT i.owner, i.table_name, i.column_name, c.data_precision,
       s.last_number,
       LEAST(s.max_value,
             POWER(10, NVL(c.data_precision, 38)) - 1) AS eff_max
FROM   dba_tab_identity_cols i
JOIN   dba_tab_columns c ON  c.owner       = i.owner
                         AND c.table_name  = i.table_name
                         AND c.column_name = i.column_name
JOIN   dba_sequences   s ON  s.sequence_owner = i.owner
                         AND s.sequence_name  = i.sequence_name
ORDER  BY s.last_number
          / LEAST(s.max_value,
                  POWER(10, NVL(c.data_precision, 38)) - 1) DESC;

The date, at the current rate

Oracle keeps no history of a sequence, so a rate needs two readings. A table and a daily insert are enough:

CREATE TABLE seq_watch (
  taken          DATE,
  sequence_owner VARCHAR2(128),
  sequence_name  VARCHAR2(128),
  last_number    NUMBER);

INSERT INTO seq_watch
SELECT SYSDATE, sequence_owner, sequence_name, last_number
FROM   dba_sequences
WHERE  sequence_owner NOT IN ('SYS', 'SYSTEM');
COMMIT;

After a week, the rate and the date:

SELECT r.sequence_owner, r.sequence_name,
       ROUND(r.per_day)                                  AS per_day,
       ROUND((s.max_value - r.last_number) / r.per_day) AS days_left
FROM  (SELECT sequence_owner, sequence_name,
              MAX(last_number) AS last_number,
              (MAX(last_number) - MIN(last_number))
                / GREATEST(MAX(taken) - MIN(taken), 1) AS per_day
       FROM   seq_watch
       WHERE  taken > SYSDATE - 7
       GROUP  BY sequence_owner, sequence_name) r
JOIN   dba_sequences s ON  s.sequence_owner = r.sequence_owner
                       AND s.sequence_name  = r.sequence_name
WHERE  r.per_day > 0
ORDER  BY days_left;

Fix it, in this order

  1. Raise the limit of the sequence. Online, instant, no downtime: ALTER SEQUENCE app.order_seq MAXVALUE 999999999999999999; or NOMAXVALUE. For an identity column: ALTER TABLE app.orders MODIFY (order_id GENERATED BY DEFAULT AS IDENTITY (MAXVALUE 999999999999999999));.
  2. Check the column first. If it is NUMBER(9), raising the sequence only changes the error to ORA-01438. Widening the precision is a dictionary change with no rewrite of the rows: ALTER TABLE app.orders MODIFY (order_id NUMBER(18));. The foreign keys that reference it, and the columns that copy it in other tables, need the same.
  3. Check the application. A 32-bit integer anywhere on the way, in the ORM, the API or the report, is the limit that no ALTER fixes. This is why the countdown should start at months, not days.
  4. Never restart or reuse the values of a key sequence. A sequence that cycles, or is restarted at 1, hands out numbers that exist in the table, and the next error is a duplicate key in the middle of the day. CYCLE is for values that are not keys.

See it coming

A sequence that runs out in three months looks fine today. Radar Engine, the detection layer of Scops, measures the daily use of every sequence and identity column itself, so no watch table of your own is needed:

DetectorClockWhen it fires
Sequence running outDaily, at the current daily use60 days: Watch. 21 days: Warning. 7 days: Critical.
Identity column running outDailyAs for sequences.

Questions

Does ALTER SEQUENCE MAXVALUE need downtime?
No. It is a dictionary change and takes effect at once. Check the column precision and the application's integer type before you rely on it.

The sequence is far below its MAXVALUE. Why does the insert fail?
Then the error is not ORA-08004. A column that is too small gives ORA-01438, and a 32-bit integer in the application fails on the application side at 2,147,483,647.

Can I just set CYCLE?
Only for values that are not keys. A cycling key sequence hands out numbers that already exist in the table, and the next error is a duplicate key.

How do I know the rate without history?
Two readings of LAST_NUMBER a day apart, or a daily insert into a table of your own as above. Scops measures the daily use itself and gives each sequence a date.

Related problems

Sources