I know what you are thinking. Why on earth would you reset a sequence? Let me tell you why.
Schema A ‘s tables are being refreshed from production to development database.
Schema A contains the data and references sequences in Schema B.
From the scheme for things, sequence > max(value) from table or sequence < max(value) from table.
max(value) from table:
LAX:(MDINH@db01)> select max(id) from t; 100
sequence > max(value):
LAX:(MDINH@db01)> select s.nextval from dual; 1978979888
Sequence reset:
LAX:(MDINH@db01)> DECLARE
l_seq NUMBER;
2 3 BEGIN
4 FOR x IN(
5 SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
6 ) LOOP
7 EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
8 SELECT s.nextval INTO l_seq FROM dual;
9 EXECUTE immediate 'alter sequence s increment by 1';
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> select s.nextval from dual;
101
sequence < max(value):
LAX:(MDINH@db01)> drop sequence s;
Sequence dropped.
LAX:(MDINH@db01)> create sequence s;
Sequence created.
LAX:(MDINH@db01)> select s.nextval from dual;
1
Sequence reset:
LAX:(MDINH@db01)> DECLARE
2 l_seq NUMBER;
3 BEGIN
4 FOR x IN(
5 SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
6 ) LOOP
7 EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
8 SELECT s.nextval INTO l_seq FROM dual;
9 EXECUTE immediate 'alter sequence s increment by 1';
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> select s.nextval from dual;
101
LAX:(MDINH@db01)>
Happy Resetting!
