sql - create - oracle id primary key autoincrement

¿La mejor manera de restablecer una secuencia de Oracle al siguiente valor en una columna existente?

Question

Por alguna razón, las personas en el pasado han insertado datos sin usar sequence.NEXTVAL. Entonces cuando voy a usar sequence.NEXTVAL para llenar una tabla, recibo una violación de PK, ya que ese número ya está en uso en la tabla.

¿Cómo puedo actualizar el siguiente valor para que se pueda usar? En este momento, solo estoy insertando una y otra vez hasta que sea exitoso ( INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL) ), y eso sincroniza el nextval.

Answer 1

Con Oracle 10.2g:

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

configurará el valor actual de la secuencia al máximo (pk) de su tabla (es decir, la próxima llamada a NEXTVAL le dará el resultado correcto); si usa Toad, presione F5 para ejecutar la instrucción, no F9, que pagina la salida (deteniendo así el incremento después de, generalmente, 500 filas). Buen lado: esta solución solo es DML, no DDL. Solo SQL y no PL-SQL. Lado malo: esta solución imprime max (pk) filas de salida, es decir, suele ser más lenta que la solución ALTER SEQUENCE.

Answer 2

En mi caso, tengo una secuencia llamada PS_LOG_SEQ que tenía un LAST_NUMBER = 3920 .

Luego importé algunos datos de PROD a mi máquina local y los PS_LOG en la tabla PS_LOG . Los datos de producción tenían más de 20000 filas con la última LOG_ID (clave principal) siendo 20070. Después de importar traté de insertar nuevas filas en esta tabla, pero al guardar recibí una excepción como esta:

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

Seguramente esto tiene que ver con la secuencia PS_LOG_SEQ asociada a la tabla PS_LOG . El LAST_NUMBER estaba colisionando con los datos que LAST_NUMBER que ya habían utilizado el siguiente valor de ID de PS_LOG_SEQ .

Para resolver eso, utilicé este comando para actualizar la secuencia a la última / max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

Este comando restableció el valor LAST_NUMBER y pude insertar nuevas filas en la tabla. No más colisión. :)

Nota: este comando de alter sequence es nuevo en Oracle 12c.

Answer 3

Estos dos procedimientos me permiten reiniciar la secuencia y restablecer la secuencia en función de los datos en una tabla (disculpas por las convenciones de codificación utilizadas por este cliente):

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;

Answer 4

Puede aumentar temporalmente el tamaño de la memoria caché y hacer una selección ficticia y luego restablecer el tamaño de la memoria caché a 1. Entonces, por ejemplo

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

Answer 5

Si puede contar con un período de tiempo en el que la tabla se encuentra en un estado estable sin inserciones nuevas, debería hacerlo (no probado):

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;

Esto le permite volver a sincronizar la secuencia con la tabla, sin descartar / recrear / volver a otorgar la secuencia. Tampoco usa DDL, por lo que no se realizan confirmaciones implícitas. Por supuesto, tendrás que perseguir y abofetear a las personas que insisten en no usar la secuencia para poblar la columna ...