ORACLE keep identity columns always as one (1)

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

---

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.