Close Menu
Oracle

Resetting Oracle Sequences: RESTART and the Classic Method

HenriqueBy Updated:10 Mins Read

This post is also available in: Português (Portuguese (Brazil))

Whether you’re rebuilding a test environment, rolling back a bulk load, or just need to restart numbering from scratch — resetting a sequence in Oracle is a common task with two very different approaches depending on your database version.

This post covers both methods, explains the impact of CACHE, how to check for dependencies before acting, and how to reset to a specific value (not just back to 1).

Before You Reset: Check for Dependencies

Resetting a sequence tied to a primary key can cause uniqueness conflicts. Always check first:

-- Sequences used as DEFAULT on columns
SELECT
    t.owner,
    t.table_name,
    t.column_name,
    t.data_default
FROM dba_tab_columns t
WHERE UPPER(t.data_default) LIKE '%SEQ%'
  AND t.owner = 'YOUR_SCHEMA';

-- Identity columns backed by internal sequences
SELECT
    owner,
    table_name,
    column_name,
    identity_column,
    default_on_null
FROM dba_tab_columns
WHERE identity_column = 'YES'
  AND owner = 'YOUR_SCHEMA';

If the sequence feeds a column that already has rows in the table, resetting to 1 will collide with existing records. In that case, reset to a value higher than the current MAX — not to 1.

Modern Method: ALTER SEQUENCE … RESTART (Oracle 18c/19c+)

⚠️ Note: The RESTART clause was originally introduced for Oracle’s internal use. It appears in the official Oracle 19c documentation, but test it in your environment before using it in production.

Starting with Oracle 18c/19c, you can restart a sequence directly:

-- Restart to original START WITH value
ALTER SEQUENCE schema.sequence_name RESTART;

-- Restart to a specific value
ALTER SEQUENCE schema.sequence_name RESTART START WITH 1000;

RESTART with no arguments reverts to the original START WITH value. To reset to a different value, use RESTART START WITH .

To also avoid gaps from pre-allocated cached values being lost on a restart:

ALTER SEQUENCE schema.sequence_name RESTART START WITH 1 NOCACHE;
-- Re-enable cache afterward if needed:
ALTER SEQUENCE schema.sequence_name CACHE 20;

Classic Method: Negative INCREMENT BY (Oracle 12c and earlier)

This method works on any Oracle version. Since the default MINVALUE of a sequence is 1, you cannot simply apply a negative INCREMENT BY equal to the current NEXTVAL — Oracle would reject it with ORA-08004, because the result would pass through 0. The solution is to temporarily lower MINVALUE to 0.

-- 1. Get the current value
SELECT schema.sequence_name.NEXTVAL FROM dual;
-- Example result: 27

-- 2. Lower MINVALUE to 0 and apply the negative increment
ALTER SEQUENCE schema.sequence_name MINVALUE 0 INCREMENT BY -27 NOCACHE;

-- 3. Consume one NEXTVAL to apply the rollback
SELECT schema.sequence_name.NEXTVAL FROM dual;
-- Returns: 0

-- 4. Restore the increment (MINVALUE is still 0 — don't change it yet)
ALTER SEQUENCE schema.sequence_name INCREMENT BY 1;

-- 5. Confirm: next NEXTVAL is 1
SELECT schema.sequence_name.NEXTVAL FROM dual;
-- Returns: 1

-- 6. Now restore MINVALUE
ALTER SEQUENCE schema.sequence_name MINVALUE 1;

CACHE caveat: If the sequence has an active CACHE, pre-allocated values in the SGA are lost on a database restart or cache flush — creating gaps in the numbering. Use NOCACHE during the reset to get a predictable result, then re-enable it afterward if needed.

Order matters: Do not try to restore INCREMENT BY 1 and MINVALUE 1 in the same command while the current value is 0 — Oracle rejects it with ORA-04007. Restore the INCREMENT BY first, consume the NEXTVAL (which delivers 1), and only then adjust MINVALUE.

To reset to a specific value (e.g., 5000), the formula is target_value - current_NEXTVAL:

-- Current NEXTVAL: 27, target: 5000
-- 5000 - 27 = 4973
ALTER SEQUENCE schema.sequence_name MINVALUE 0 INCREMENT BY 4973 NOCACHE;
SELECT schema.sequence_name.NEXTVAL FROM dual;
ALTER SEQUENCE schema.sequence_name INCREMENT BY 1;

Identity Columns: A Special Case

If the column uses GENERATED AS IDENTITY (introduced in Oracle 12c), it has an internally managed sequence. Direct ALTER SEQUENCE does not apply — use the table syntax instead:

ALTER TABLE schema.table_name
  MODIFY (column_name GENERATED AS IDENTITY (START WITH 1));

Or to a specific value:

ALTER TABLE schema.table_name
  MODIFY (column_name GENERATED AS IDENTITY (START WITH 5000));

Full Lab Scenario

Setup

CREATE TABLE lab_orders (
    id     NUMBER PRIMARY KEY,
    descr  VARCHAR2(100)
);

CREATE SEQUENCE lab_seq START WITH 1 INCREMENT BY 1 CACHE 20;

Insert initial rows

INSERT INTO lab_orders VALUES (lab_seq.NEXTVAL, 'Order A');
INSERT INTO lab_orders VALUES (lab_seq.NEXTVAL, 'Order B');
INSERT INTO lab_orders VALUES (lab_seq.NEXTVAL, 'Order C');
COMMIT;

SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 4

Modern Method — RESTART (19c+)

ALTER SEQUENCE lab_seq RESTART START WITH 1 NOCACHE;

SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 1

SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 2

SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 3

RESTART delivers exactly the value defined in START WITH, no surprises.

Classic Method — Negative INCREMENT BY

To demonstrate the classic method, continue inserting rows from the current state (NEXTVAL = 3 after the test above) until you have a larger accumulated value:

INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido D');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido E');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido F');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido G');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido H');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido I');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido J');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido K');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido L');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido M');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido N');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido O');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido P');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido Q');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido R');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido S');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido T');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido U');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido V');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido W');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido X');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido Y');
INSERT INTO lab_pedidos VALUES (lab_seq.NEXTVAL, 'Pedido Z');
COMMIT;

SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 27

Now the reset:

-- Step 1: lower MINVALUE to 0 and apply the negative increment
ALTER SEQUENCE lab_seq MINVALUE 0 INCREMENT BY -27 NOCACHE;

-- Step 2: consume one NEXTVAL to apply the rollback
SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 0

-- Step 3: restore INCREMENT BY
ALTER SEQUENCE lab_seq INCREMENT BY 1;

-- Step 4: confirm the reset
SELECT lab_seq.NEXTVAL FROM dual;
-- Returns: 1

-- Step 5: restore MINVALUE
ALTER SEQUENCE lab_seq MINVALUE 1;

Cleanup

DROP SEQUENCE lab_seq;
DROP TABLE lab_orders;

Known Errors

ORA-08004: sequence NEXTVAL goes below MINVALUE and cannot be instantiated

Occurs when the negative INCREMENT BY would push NEXTVAL below the sequence’s MINVALUE (default: 1). Applying INCREMENT BY -27 with NEXTVAL at 27 would land on 0, violating MINVALUE.

Solution: Lower MINVALUE to 0 before applying the negative increment:

ALTER SEQUENCE lab_seq MINVALUE 0 INCREMENT BY -27 NOCACHE;
SELECT lab_seq.NEXTVAL FROM dual; -- Returns: 0
ALTER SEQUENCE lab_seq INCREMENT BY 1;
ALTER SEQUENCE lab_seq MINVALUE 1;

ORA-04007: MINVALUE cannot be made to exceed the current value

Occurs when you try to restore MINVALUE 1 while the sequence’s current value is 0. Oracle does not allow MINVALUE to be set higher than the current value.

Solution: Restore INCREMENT BY 1 first and consume one NEXTVAL (which delivers 1) before adjusting MINVALUE:

-- WRONG — fails with ORA-04007:
ALTER SEQUENCE lab_seq INCREMENT BY 1 MINVALUE 1;

-- CORRECT — do it in steps:
ALTER SEQUENCE lab_seq INCREMENT BY 1;
SELECT lab_seq.NEXTVAL FROM dual; -- Returns: 1
ALTER SEQUENCE lab_seq MINVALUE 1;

Quick Reference

-- Modern (19c+): simple restart
ALTER SEQUENCE schema.seq RESTART;

-- Modern (19c+): restart to specific value
ALTER SEQUENCE schema.seq RESTART START WITH 1000;

-- Classic: replace X with current NEXTVAL
ALTER SEQUENCE schema.seq MINVALUE 0 INCREMENT BY -X NOCACHE;
SELECT schema.seq.NEXTVAL FROM dual;   -- Returns: 0
ALTER SEQUENCE schema.seq INCREMENT BY 1;
SELECT schema.seq.NEXTVAL FROM dual;   -- Returns: 1
ALTER SEQUENCE schema.seq MINVALUE 1;

-- Identity column
ALTER TABLE schema.table_name
  MODIFY (column_name GENERATED AS IDENTITY (START WITH 1));

References

Share.

Related Posts

Add A Comment
Leave A Reply