Oracle reset sequence

Da Emigar.
Versione del 12 nov 2014 alle 17:58 di WikiSysop (discussione | contributi) (Creata pagina con " <pre> 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...")
(diff) ← Versione meno recente | Versione attuale (diff) | Versione più recente → (diff)
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';