时间戳

首页 > 解决方案 > ORACLE 始终将标识列保持为一 (1)

sql - ORACLE 始终将标识列保持为一 (1)

问题描述

我发现了一些与我类似的问题，但我找不到确切的解决方案。在我们拥有的数据仓库中，我们有时会在修复某些问题或其他类似问题时“删除”或“截断”表。但是，在Oracle中，标识列总是从下一个字符串开始，如何让字符串总是从1开始呢？这可能会破坏其他方面和事实。

是否可以永远或至少在插入过程中放置​​此配置？

我总是通过 SQL Developer 手动重置标识列，因为我不知道如何通过 PL/SQL 进行重置，如下所示（软件是葡萄牙语，抱歉）。

创建的标识列示例：

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
)

在此处输入图像描述

谢谢！

标签： sqloracleoracle-sqldeveloper

解决方案

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.

推荐阅读