Oracle 9 - 테이블 상태와 일치하도록 시퀀스 재설정

Oracle 9 - 테이블 상태와 일치하도록 시퀀스 재설정

Oracle 테이블에 정수 기반 기본 키를 시드하는 데 사용되는 시퀀스가 있습니다.

이 시퀀스가 항상 새 값을 표에 삽입하는 데 사용되지는 않은 것 같습니다.순서를 표의 실제 값과 보조를 맞추려면 어떻게 해야 합니까?

ID가 PK 열의 이름이고 PK_SEQ가 시퀀스의 이름인 경우:

1. 테이블 이름에서 MAX(ID)를 선택하여 가장 높은 PK 값 찾기

2. PK_SEQ.NEXTVAL FROM DUAL을 선택하여 다음 PK_SEQ 값 찾기

3. #2 > #1인 경우 이러한 값을 실제 대리 키로 처리한다고 가정하면 아무것도 수행할 필요가 없습니다.
4. 그렇지 않으면 ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value]에 의해 최대 ID로 점프하도록 시퀀스를 변경합니다.

5. 듀얼에서 PK_SEQ.NEXTVAL을 선택하여 시퀀스를 범핑합니다.

6. ALTER SEQUENCE PK_SEQ INCREMENT BY 1을 사용하여 시퀀스 증분 값을 1로 재설정합니다.

이 모든 것은 당신이 이것을 하는 동안 테이블에 새로운 삽입물이 없다는 것을 가정합니다.

간단히 말해서, 게임하기:

-- Current sequence value is 1000

ALTER SEQUENCE x INCREMENT BY -999;
Sequence altered.

SELECT X.NEXTVAL FROM DUAL;
1

ALTER SEQUENCE x INCREMENT BY 1;
Sequence altered.

테이블 내에서 사용되는 최대 시퀀스 값을 가져오고, 계산을 수행하고, 그에 따라 시퀀스를 업데이트할 수 있습니다.

Declare
  difference INTEGER;
  sqlstmt varchar2(255);
  sequenceValue Number;
begin
sqlstmt := 'ALTER SEQUENCE YOURSEQUENCE INCREMENT BY ';
select YOURSEQUENCE.NEXTVAL into sequenceValue from dual;
select  (nvl(Max(YOURID),0) - sequenceValue)+1 into difference from YOURTABLE;
if difference > 0 then
  EXECUTE IMMEDIATE sqlstmt || difference;
  select  YOURSEQUENCE.NEXTVAL INTO sequenceValue from dual;
  EXECUTE IMMEDIATE sqlstmt || 1;
end if;
end;

온라인에서 내 모든 시퀀스를 현재 가장 높은 ID로 동적으로 설정하는 스크립트를 찾지 못해 이 스크립트를 만들었습니다.Oracle 11.2.0.4에서 테스트되었습니다.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;

경우에 따라 현재 max 값을 간단하게 얻은 다음

drop sequence x;
create sequence x start with {current max + 1};

당신이 드롭을 한 후에 앱이 깨질 것입니다.그러나 그 기간 동안 아무도 행을 삽입할 수 없으며, 시퀀스를 만드는 것이 빠릅니다.시퀀스가 다음과 같을 때 해당 권한이 삭제되므로 해당 권한을 시퀀스에 다시 생성해야 합니다.그리고 시퀀스에 따라 달라지는 plsql을 수동으로 다시 컴파일할 수도 있습니다.

https://stackoverflow.com/a/15929548/1737973, 에 추가되지만 에 의존하지는 않습니다.SEQUENCENAME.NEXTVAL따라서 다음과 같이 한 위치에 위치하지 않아야 합니다.

DECLARE
  difference INTEGER;
  alter_sequence_statement VARCHAR2 (255);
  sequence_value NUMBER;
BEGIN
  --   Base for the statement that will set the sequence value.
  alter_sequence_statement :=
      'ALTER SEQUENCE SEQUENCENAME INCREMENT BY ';

  --   Fetch current last sequence value used.
  SELECT
    --   You could maybe want to make some further computations just
    -- below if the sequence is using caching.
    last_number
  INTO sequence_value
  FROM all_sequences
  WHERE sequence_owner = 'SEQUENCEOWNER' AND sequence_name = 'SEQUENCENAME';

  --   Compute the difference.
  SELECT max(id) - sequence_value + 1 INTO difference
  FROM SCHEMANAME.TABLENAME;

  IF difference <> 0 THEN
    --   Set the increment to a big offset that puts the sequence near
    -- its proper value.
    EXECUTE IMMEDIATE alter_sequence_statement || difference;

    --   This 'sequence_value' will be ignored, on purpose.
    SELECT SEQUENCENAME.NEXTVAL INTO sequence_value FROM dual;

    --   Resume the normal pace of incrementing one by one.
    EXECUTE IMMEDIATE alter_sequence_statement || 1;
  END IF;
END;

고지 사항: 시퀀스가 캐싱(all_sequences.cache_size0보다 큼으로 설정)을 사용하는 경우 차이 계산 단계에서 이를 고려할 수 있습니다.

에 대한 오라클 문서...

• 테이블에서 최대값을 선택하고 시퀀스 값을 설정합니다.

SELECT setval( 'table_id_seq_name', (table_name에서 MAX(id));

언급URL : https://stackoverflow.com/questions/1426647/oracle-9-resetting-sequence-to-match-the-state-of-the-table