Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dept
  (id_dept NUMBER(10,0)
     GENERATED BY DEFAULT ON NULL AS IDENTITY
       MINVALUE 1
       MAXVALUE 9999999999999999999999999999
       INCREMENT BY 1
       START WITH 1
       CACHE 20
       NOORDER
       NOCYCLE
       NOKEEP
       NOSCALE
     NOT NULL ENABLE)
     ;
CREATE OR REPLACE FUNCTION get_column_sequence_name(p_table_name  IN VARCHAR2,
                                                    p_column_name IN VARCHAR2)
  RETURN VARCHAR2
AS
  v_data_default LONG;
  v_result       VARCHAR2(32767);
BEGIN
  SELECT DATA_DEFAULT
    INTO v_data_default
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = p_table_name AND
          COLUMN_NAME = p_column_name;

  v_result := SUBSTR(v_data_default, 1, 32767);

  RETURN SUBSTR(v_result, 1, INSTR(v_result, '.', -1)-1);
END GET_COLUMN_SEQUENCE_NAME;
/
CREATE OR REPLACE PROCEDURE insert_empty_row(p_table_name VARCHAR2)
AS
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO ' || p_table_name || ' VALUES (NULL)';
  COMMIT;
END;
/
CREATE OR REPLACE FUNCTION RESET_SEQUENCE(p_table_name    IN VARCHAR2,
                                          p_column_name   IN VARCHAR2)
  RETURN NUMBER
AS
  v_num  NUMBER;
  v_sql varchar2(200);
  v_seq varchar2(200);
BEGIN
  v_seq := get_column_sequence_name(p_table_name, p_column_name);
  DBMS_OUTPUT.PUT_LINE('tryint to reset sequence''' || v_seq || '''');
  -- Get the next value from the sequence
  EXECUTE IMMEDIATE 'SELECT ' || v_seq || '.NEXTVAL ' ||
                    '  FROM DUAL'
    INTO v_num;
  
  -- Change the sequence so it decrements or increments to the desired
  -- start value the next time NEXTVAL is invoked.
  
  v_sql := 'ALTER TABLE ' || p_table_name || ' MODIFY ('
  || p_column_name || ' GENERATED BY DEFAULT ON NULL AS IDENTITY (INCREMENT BY '
  || (-v_num)  || ' MINVALUE 0))' ;
  EXECUTE IMMEDIATE v_sql;

  -- Decrement/increment the sequence to the desired start value
  EXECUTE IMMEDIATE 'SELECT ' || v_seq || '.NEXTVAL FROM DUAL'
    INTO v_num;
  
  -- Reset the sequence so it uses the desired "increment-by"
  v_sql := 'ALTER TABLE ' || p_table_name || ' MODIFY ('
  || p_column_name || ' GENERATED BY DEFAULT ON NULL AS IDENTITY (INCREMENT BY 1))' ;
  EXECUTE IMMEDIATE v_sql;
  
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('RESET_SEQUENCE : ' || SQLCODE || ' ' || SQLERRM);
    RETURN 0;
END RESET_SEQUENCE;
/


DECLARE
  v_sequence_name  VARCHAR2(32767);
  v_table_name     VARCHAR2(30) := 'DEPT';
  v_column_name    VARCHAR2(30) := 'ID_DEPT';
  nVal              NUMBER;
  nReset            NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_table_name;
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);

  nReset := RESET_SEQUENCE(v_table_name, v_column_name);
  DBMS_OUTPUT.PUT_LINE('Resetted: ' || nReset);
  -- Truncate table
END;
/
1 rows affected

dbms_output:
tryint to reset sequence'"FIDDLE_HVJMDCGRKHSAOAVQDCXT"."ISEQ$$_235997"'
Resetted: 1

SELECT * FROM dept


ID_DEPT


1

2

3

4


DECLARE
  v_sequence_name  VARCHAR2(32767);
  v_table_name     VARCHAR2(30) := 'DEPT';
  v_column_name    VARCHAR2(30) := 'ID_DEPT';
  nVal              NUMBER;
  nReset            NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_table_name;
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);
  insert_empty_row(v_table_name);

  nReset := RESET_SEQUENCE(v_table_name, v_column_name);
  DBMS_OUTPUT.PUT_LINE('Resetted: ' || nReset);
  -- Truncate table
END;
/
1 rows affected

dbms_output:
tryint to reset sequence'"FIDDLE_HVJMDCGRKHSAOAVQDCXT"."ISEQ$$_235997"'
Resetted: 1

SELECT * FROM dept


ID_DEPT


1

2

3

4