Oracle 시퀀스를 기존 열의 다음 값으로 리셋하는 가장 좋은 방법은 무엇입니까?

Oracle 시퀀스를 기존 열의 다음 값으로 리셋하는 가장 좋은 방법은 무엇입니까?

어떤 이유에서인지 과거 사람들은 시퀀스를 사용하지 않고 데이터를 삽입했다.넥스트밸그래서 제가 시퀀스를 사용할 때.테이블을 채우기 위해 NEXTVAL에서 PK 위반이 발생합니다.이 번호는 테이블에서 이미 사용되고 있기 때문입니다.

다음 값을 사용할 수 있도록 업데이트하려면 어떻게 해야 합니까?지금은 성공할 때까지 몇 번이고 계속 삽입하고 있습니다( )INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)다음 값이 동기화됩니다.

캐시 크기를 일시적으로 늘리고 더미 선택을 한 후 캐시 크기를 1로 리셋할 수 있습니다.예를 들면

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

제 경우엔 '나'라는 시퀀스가 있어요PS_LOG_SEQ그 안에 있는LAST_NUMBER = 3920.

그런 다음 데이터 Import를 했습니다.PROD로컬 머신에 접속하여PS_LOG테이블. 프로덕션 데이터는20000최신 LOG_ID(프라이머리 키)가 20070인 행.Import 후 이 테이블에 새 행을 삽입하려고 했는데 저장할 때 다음과 같은 예외가 발생했습니다.

ORA-00001: unique constraint (LOG.PS_LOG_PK) violated

확실히 이건 수열과 관련이 있어PS_LOG_SEQ관련지어져 있다PS_LOG테이블.LAST_NUMBER이미 다음 ID 값을 사용한 Import한 데이터와 충돌하고 있습니다.PS_LOG_SEQ.

이 문제를 해결하기 위해 이 명령을 사용하여 시퀀스를 최신으로 업데이트했습니다.\max(LOG_ID)+ 1:

alter sequence PS_LOG_SEQ restart start with 20071;

이 명령어는 를 리셋합니다.LAST_NUMBER그러면 테이블에 새 행을 삽입할 수 있습니다.더 이상 충돌은 없습니다.:)

주의: 이 명령어는 Oracle 12c에서 새로 도입된 명령어입니다.

주의: 이 문서는 ALTER SEQUENCE RESTART 옵션이 존재하지만 18c 현재 문서화되어 있지 않습니다.이는 Oracle 내부 사용을 의도한 것으로 보입니다.

다음의 2개의 순서를 사용하면, 시퀀스를 리셋 해, 테이블내의 데이터에 근거해 시퀀스를 리셋 할 수 있습니다(이 클라이언트에 의해서 사용되는 부호화 표기법에 대한 애플리케이션).

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

테이블이 새로운 삽입이 진행되지 않고 안정된 상태에 있는 시간을 기대할 수 있는 경우 다음과 같이 합니다(테스트되지 않음).

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

이를 통해 시퀀스를 폐기/재작성/재작성하지 않고 테이블과 동기화된 상태로 되돌릴 수 있습니다.또한 DDL을 사용하지 않기 때문에 암묵적인 커밋은 실행되지 않습니다.물론, 당신은 칼럼을 채우는 데 시퀀스를 사용하지 않겠다고 고집하는 사람들을 추적해서 따귀를 때려야 할 것입니다.

Oracle 10.2g의 경우:

select  level, sequence.NEXTVAL
from  dual 
connect by level <= (select max(pk) from tbl);

는 현재 시퀀스 값을 테이블의 max(pk)로 설정합니다(즉, NEXTVAL에 대한 다음 호출은 올바른 결과를 제공합니다). Toad를 사용하는 경우 F9이 아닌 F5를 눌러 출력을 호출합니다(따라서 보통 500행 후에 증분 처리를 중지합니다).좋은 점: 이 솔루션은 DDL이 아닌 DML뿐입니다.SQL만 있고 PL-SQL은 없습니다.나쁜 점: 이 솔루션은 출력의 최대(pk) 행을 출력합니다.즉, 보통 ALTER SEQUENCE 솔루션보다 속도가 느립니다.

현재 Oracle 12c 이상에서는 열이 GENTERED...로 정의되어 있을 수 있습니다.AS ID 및 Oracle이 시퀀스 자체를 처리합니다.

ALTER TABLE 문을 사용하여 ID의 "START WITH"를 수정할 수 있습니다.

ALTER TABLE tbl MODIFY ("ID" NUMBER(13,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3580 NOT NULL ENABLE);

내 경우, 나는 시퀀스를 0으로 리셋하고 타겟 테이블의 0에서 최대값까지 설정하는 방법을 사용했다.

DECLARE
    last_val NUMBER;
    next_val NUMBER;
BEGIN
    SELECT MAX(id_field) INTO next_val FROM some_table;
    IF next_val > 0 THEN
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY -' || last_val || ' MINVALUE 0';
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY ' || next_val;
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY 1 MINVALUE 1';
    END IF;
END;

제 프로그램이 노드오라클과 함께 Typeorm에서 실행되기 때문에 원라이너 솔루션을 제공하지 못해 죄송합니다.하지만 다음 SQL 명령어가 이 문제에 도움이 될 것 같습니다.

★★★★★★★★★★★를 손에 넣다LAST_NUMBER신신의당

SELECT SEQUENCE_NAME, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '${sequenceName}'

마지막 행에서 PK 값을 가져옵니다(이 경우 ID는 PK).

SELECT ID FROM ${tableName} ORDER BY ID DESC FETCH NEXT 1 ROWS ONLY

으로 업데이트 ★★★★★LAST_NUMBER+ 1420 + 1:420으로 합니다.

ALTER SEQUENCE ${sequenceName} RESTART START WITH ${value + 1}

언급URL : https://stackoverflow.com/questions/6099108/best-way-to-reset-an-oracle-sequence-to-the-next-value-in-an-existing-column