How do I reset a sequence in Oracle?

For regular sequences:

alter sequence serial restart start with 1;

For system-generated sequences used for identity columns:

alter table table_name modify id generated by default on null as identity(start with 1);

---

This feature was officially added in 18c but is unofficially available since 12.1.

It is arguably safe to use this undocumented feature in 12.1. Even though the syntax is not included in the official documentation, it is generated by the Oracle package DBMS_METADATA_DIFF. I've used it several times on production systems. However, I created an Oracle Service request and they verified that it's not a documentation bug, the feature is truly unsupported.

In 18c, the feature does not appear in the SQL Language Syntax, but is included in the Database Administrator's Guide.

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.

[email protected]> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?

This is my approach:

1. drop the sequence
2. recreate it

Example:

--Drop sequence

DROP SEQUENCE MY_SEQ;

-- Create sequence 

create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;

A true restart is not possible AFAIK. (Please correct me if I'm wrong!).

However, if you want to set it to 0, you can just delete and recreate it.

If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.

That is, if your sequence is at 500, you can set it to 100 via

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;