ORACLE keep identity columns always as one (1)

Question

I found some questions similar to mine, but I couldn't find the exact solution. In the Data Warehouse we have, we sometimes "Delete" or "Truncate" the table when we fix something or some other similar problem. However, in Oracle, the identity column always starts with the next string, how do you make the string always start with 1? This can disrupt the other dimensions and facts.

Is it possible to put this configuration forever or at least in the insert procedure?

I always reset the identity column manually by SQL Developer, since I don't know how to do it by PL/SQL, as shown below (software is in Portuguese, sorry).

Example of identity column created:

CREATE TABLE "DW_FUNCESP"."D_DEPARTAMENTO"
(
    "ID_DEPARTAMENT" 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
)

Thanks!

Answer 1

NOTE:

full example has been prepared as a separate answer, I've just slightly adjusted the solution to use the ALTER TABLE clauses (as agreed) full credits to @Bob Jarvis

---

As already mentioned in the comments, Oracle IDENTITY columns are just backed by a system created SEQUENCE. Some details could be found for example here (with proper links to official Oracle docs).

Oracle doesn't have a simple way to reset the sequence value, however here's an extensive discussion on how to do that using quite simple steps

Unfortunately (though could be fortunately) the system-created sequences are not allowed to be altered that way, however the sequence attributes could be changed by running ALTER TABLE MODIFY commands. An extract could be seen here

BEGIN
  -- Get the next value from the sequence
  EXECUTE IMMEDIATE 'SELECT ' || v_sequence || '.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_sequence || '.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;
END;
/

And full example on dbfiddle

Answer 2

---

EDIT: Given what I knew at the time I was right, but as it turns out I'm incorrect. In a comment and an associated fiddle @micklesh shows how this can be accomplished using ALTER TABLE MODIFY.... I've asked him to post an answer so it can be accepted - in the meantime I've leaving this answer here so that others can at least follow the link to his dbfiddle. But really - this is incorrect.

---

Sorry, but you cannot do this.

I had a really nice answer prepared about how to get the name of the sequence the system creates for a GENERATED ALWAYS AS IDENTITY column, and how to translate that LONG value to a character string, and how to reset the start value of a sequence - all good stuff, and it made a really nice db<>fiddle - and then when I got it all wrapped up I made one last pass through it - and got

ORA-32793: cannot alter a system-generated sequence

So - Oracle will not let you alter the sequence it generates for a GENERATED ALWAYS AS IDENTITY column. I think this means you're stuck and you're going to have to live with the fact that those numbers cannot be reset to start at one. Your other options would be to

1. Drop and recreate the table, which would also require you to recreate any associated triggers, and recompile any procedures/functions/packages which use this table, and probably other things I haven't thought of; or

2. Don't use GENERATE ALWAYS AS IDENTITY, create your own sequence, use a trigger to set the identity column from your sequence, and then you should be able to use the following procedure to reset your sequence:

CREATE OR REPLACE FUNCTION RESET_SEQUENCE(pinSequence    IN VARCHAR2,
                                          pinStart_value IN NUMBER DEFAULT 1,
                                          pinIncrement   IN NUMBER DEFAULT 1)
  RETURN NUMBER
AS
  nVal  NUMBER;
BEGIN
  -- Get the next value from the sequence

  EXECUTE IMMEDIATE 'SELECT ' || pinSequence || '.NEXTVAL ' ||
                    '  FROM DUAL'
    INTO nVal;

  -- Change the sequence so it decrements or increments to the desired
  -- start value the next time NEXTVAL is invoked.

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pinSequence ||
                    '  INCREMENT BY ' || (nVal - (pinStart_value - pinIncrement)) * -1 ||
                    '  MINVALUE 0';

  -- Decrement/increment the sequence to the desired start value

  EXECUTE IMMEDIATE 'SELECT ' || pinSequence || '.NEXTVAL ' ||
                    '  FROM DUAL'
    INTO nVal;

  -- Reset the sequence so it uses the desired "increment-by"

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pinSequence ||
                    '  INCREMENT BY ' || pinIncrement ||
                    '  MINVALUE 0';

  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('RESET_SEQUENCE : ' || SQLCODE || ' ' || SQLERRM);
    RETURN 0;
END RESET_SEQUENCE;
/

And here's a db<>fiddle showing a general test of the RESET_SEQUENCE function.

Pax.