Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
4 views
in SQL by (20.3k points)

In PostgreSQL, I can do something like this:

ALTER SEQUENCE serial RESTART WITH 0;

Is there an Oracle equivalent?

1 Answer

0 votes
by (40.7k points)

Following is the good method for resetting any sequence to 0 from Oracle:

[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;

/

For more information, you can refer to these links: Dynamic SQL to reset sequence value & How to reset sequences?

Related questions

0 votes
2 answers
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Dec 4, 2020 in SQL by dev_sk2311 (45k points)

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...