Oracle reset sequence
Jump to navigation
Jump to search
declare
NN number;
begin
for c1 in (select LAST_NUMBER,INCREMENT_BY,SEQUENCE_NAME,MIN_VALUE,SEQUENCE_OWNER from dba_sequences where sequence_owner='NDOC')
loop
begin
execute immediate 'alter sequence ' || c1.sequence_owner || '.' || c1.sequence_name || ' increment by ' || ( (c1.last_number-1) * -1 ) || '' ;
execute immediate 'alter sequence ' || c1.sequence_owner || '.' || c1.sequence_name || ' minvalue ' || (c1.min_value - 1) || '' ;
execute immediate 'select ' || c1.sequence_owner || '.' || c1.sequence_name || '.NEXTVAL from dual' into NN;
execute immediate 'commit';
execute immediate 'alter sequence ' || c1.sequence_owner || '.' || c1.sequence_name || ' increment by ' || c1.increment_by || '' ;
execute immediate 'alter sequence ' || c1.sequence_owner || '.' || c1.sequence_name || ' minvalue ' || c1.min_value || '' ;
exception
when others
then
null;
end;
end loop;
end;
/
commit;
select LAST_NUMBER,INCREMENT_BY,SEQUENCE_NAME from dba_sequences where sequence_owner='NDOC';