CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS
 
cval   INTEGER;
inc_by VARCHAR2(25);
 
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
 
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
 
  cval := cval - startvalue + 1;
 
  IF cval = 0 THEN
    RETURN;
  ELSIF cval < 0 THEN 
    inc_by := ' INCREMENT BY ' || ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -' || cval;
  END IF; 
 
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by;
 
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
 
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
 
END reset_sequence;
/
 
CREATE OR REPLACE PROCEDURE reset_sequence_from_table (
seq_name IN VARCHAR2, column_name IN VARCHAR2, table_name IN VARCHAR2) AS
 
cval   INTEGER;
 
BEGIN
 
  EXECUTE IMMEDIATE 'SELECT MAX(' || column_name ||') FROM ' || table_name 
  INTO cval;
 
  IF (cval IS NULL) THEN
    cval := 1;
  ELSE
    cval := cval + 1;
  END IF;
 
  RESET_SEQUENCE(seq_name, cval);
 
END reset_sequence_from_table;
/

DROP sequence mytable_seq;
DROP TABLE mytable;
 
CREATE TABLE mytable ( id NUMBER );
CREATE sequence mytable_seq;
 
INSERT INTO mytable VALUES (1);
INSERT INTO mytable VALUES (2);
INSERT INTO mytable VALUES (3);
INSERT INTO mytable VALUES (4);
INSERT INTO mytable VALUES (5);
INSERT INTO mytable VALUES (6);
INSERT INTO mytable VALUES (7);
INSERT INTO mytable VALUES (8);
INSERT INTO mytable VALUES (9);
INSERT INTO mytable VALUES (10);
 
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
SELECT mytable_seq.NEXTVAL FROM dual;
 
COMMIT;

exec reset_sequence_from_table('MYTABLE_SEQ', 'ID', 'MYTABLE');
 
-- should return 11 because mytable has a record with id 10
SELECT mytable_seq.NEXTVAL FROM dual;