language/DB

sequence reset sequence

영화보는아이 2013. 9. 10. 12:59

http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle 


CREATE or REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as

      curr_val INTEGER;

   BEGIN

     EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';

       EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;

       EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by -'||curr_val;

       EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;

      EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1';

    END reset_sequence;