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