Oracle reset sequence

Da Emigar.
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';