Reset An Oracle SEQUENCE

reset-oracle-sequence.sql

SET DEFINE OFF;
SET SERVEROUTPUT ON;
SET FEEDBACK OFF; 

--/
DECLARE
    newStartNumber INTEGER;
    oldStartNumber INTEGER;
    difference INTEGER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT NVL(MAX(EXAMPLE_ID) + 1, 1)  FROM TABLE_EXAMPLES' INTO newStartNumber;
    EXECUTE IMMEDIATE 'SELECT EXAMPLES_SEQ.NEXTVAL FROM DUAL' INTO oldStartNumber;
    difference := newStartNumber - oldStartNumber;
    EXECUTE IMMEDIATE 'DROP SEQUENCE EXAMPLES_SEQ';
    EXECUTE IMMEDIATE
        'CREATE SEQUENCE EXAMPLES_SEQ' ||
        '  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 ' ||
        'START WITH ' || newStartNumber ||
        ' NOCACHE NOORDER NOCYCLE';
    EXECUTE IMMEDIATE 'GRANT SELECT ON EXAMPLES_SEQ TO TABLE_U';
    DBMS_OUTPUT.PUT_LINE('Reset SEQUENCE EXAMPLES_SEQ.NEXTVAL = ' || newStartNumber || ' - changed by (' || difference || ')');
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Reset SEQUENCE `EXAMPLES_SEQ` ERROR: ' || SQLERRM);
END;
/