update - Oracle 9: restableciendo la secuencia para que coincida con el estado de la tabla

set sequence value oracle

Question

Tengo una secuencia utilizada para inicializar mis claves primarias (basadas en enteros) en una tabla de Oracle.

Parece que esta secuencia no siempre se ha utilizado para insertar nuevos valores en la tabla. ¿Cómo puedo obtener la secuencia de nuevo en el paso con los valores reales en la tabla?

Answer 1

En algunos casos, puede que le resulte más fácil simplemente obtener el valor máximo actual y luego

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

La aplicación se romperá después de que hagas la caída. Pero eso evitará que cualquiera inserte filas durante ese período, y crear una secuencia es rápido. Asegúrese de volver a crear cualquier concesión en la secuencia, ya que se eliminarán cuando la secuencia sea. Y es posible que desee recompilar manualmente cualquier plsql que dependa de la secuencia.

Answer 2

En definitiva, el juego:

-- 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.

Puede obtener el valor máximo de secuencia utilizado en su tabla, hacer los cálculos y actualizar la secuencia en consecuencia.

Answer 3

Hice este script porque no encontré un script en línea que establece dinámicamente todas mis secuencias en el ID más alto actual. Probado en 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;

Answer 4

La adición a https://.com/a/15929548/1737973 , pero sin tener que recurrir a SEQUENCENAME.NEXTVAL por lo tanto, no debe dar como resultado una posición:

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;

Descargo de responsabilidad: si la secuencia está utilizando el almacenamiento en caché ( all_sequences.cache_size establecido a más de 0) probablemente desee tenerlo en cuenta en el paso Calcular la diferencia .

Documentación de Oracle para all sequences ....

Answer 5

Si ID es el nombre de su columna PK y PK_SEQ es el nombre de su secuencia:

1. Encuentre el valor de la PK más alta con SELECT MAX (ID) FROM tableName

2. Encuentre el valor de la próxima PK_SEQ SELECT PK_SEQ.NEXTVAL FROM DUAL

3. Si # 2> # 1 entonces no se necesita hacer nada, asumiendo que usted trata estos valores como verdaderas claves sustitutas
4. De lo contrario, modifique la secuencia para saltar a la ID máxima mediante ALTER SEQUENCE PK_SEQ INCREMENT BY BY [# 1 value - # 2 value]

5. Golpee la secuencia SELECT PK_SEQ.NEXTVAL FROM DUAL

6. Restablezca el valor de incremento de secuencia a 1 mediante ALTER SEQUENCE PK_SEQ INCREMENT BY BY

Todo esto supone que no tienes nuevas inserciones en la mesa mientras haces esto ...

Answer 6

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;