Solución:
Las secuencias no están realmente diseñadas para reiniciarse. Pero hay algunos casos en los que es deseable restablecer una secuencia, por ejemplo, al configurar datos de prueba o fusionar datos de producción en un entorno de prueba. Este tipo de actividad es no normalmente hecho en producción.
SI este tipo de operación se va a poner en producción, debe probarse a fondo. (Lo que causa más preocupación es la posibilidad de que el procedimiento de reinicio se realice accidentalmente en el momento equivocado, por ejemplo, a mediados de año.
Dejar caer y recrear la secuencia es un enfoque. Como operación, es bastante sencillo en lo que respecta a la SECUENCIA:
DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;
[EDIT] Como señala correctamente Matthew Watson, cada declaración DDL (como DROP, CREATE, ALTER) provocará una confirmación implícita. [/EDIT]
Sin embargo, todos los privilegios otorgados en la SECUENCIA se eliminarán, por lo que será necesario volver a otorgarlos. Se invalidará cualquier objeto que haga referencia a la secuencia. Para generalizar esto, necesitaría guardar privilegios (antes de descartar la secuencia) y luego volver a otorgarlos.
Un segundo enfoque es ALTERAR una SECUENCIA existente, sin dejarla caer y volver a crearla. El restablecimiento de la secuencia se puede lograr cambiando el valor de INCREMENTO a un valor negativo (la diferencia entre el valor actual y 0), y luego hacer exactamente un .NEXTVAL para establecer el valor actual en 0, y luego cambiar el INCREMENTO de nuevo a 1. He usado este mismo enfoque antes (manualmente, en un entorno de prueba), para establecer una secuencia en un valor mayor también.
Por supuesto, para que esto funcione correctamente, necesita asegurar ninguna otra sesión hace referencia a la secuencia mientras se realiza esta operación. Un .NEXTVAL adicional en el momento equivocado arruinará el reinicio. (NOTA: lograr eso en el lado de la base de datos será difícil, si la aplicación se conecta como propietaria de la secuencia, en lugar de como un usuario separado).
Para que suceda todos los años, debe programar un trabajo. El restablecimiento de la secuencia deberá coordinarse con el restablecimiento de la parte YYYY de su identificador.
He aquí un ejemplo:
http://www.jaredstill.com/content/reset-sequence.html
[EDIT]
NO PROBADO marcador de posición para un posible diseño de un bloque PL / SQL para restablecer la secuencia
declare
pragma autonomous_transaction;
ln_increment number;
ln_curr_val number;
ln_reset_increment number;
ln_reset_val number;
begin
-- save the current INCREMENT value for the sequence
select increment_by
into ln_increment
from user_sequences
where sequence_name="MY_SEQ";
-- determine the increment value required to reset the sequence
-- from the next fetched value to 0
select -1 - MY_SEQ.nextval into ln_reset_increment from dual;
-- fetch the next value (to make it the current value)
select MY_SEQ.nextval into ln_curr from dual;
-- change the increment value of the sequence to
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_reset_increment ||' minvalue 0';
-- advance the sequence to set it to 0
select MY_SEQ.nextval into ln_reset_val from dual;
-- set increment back to the previous(ly saved) value
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_increment ;
end;
/
NOTAS:
- ¿Cuál es la mejor forma de proteger la secuencia del acceso mientras se reinicia? ¿CAMBIAR EL NOMBRE?
- Varios casos de prueba para trabajar aquí.
- Primero pase, verifique los casos normativos de secuencia positiva, ascendente, de incremento 1.
- ¿Sería un mejor enfoque crear una nueva SECUENCIA, agregar permisos, cambiar el nombre de las secuencias existentes y nuevas y luego volver a compilar las dependencias?
Solo lanzando esto como una idea:
Si desea una solución que no requiera DDL en curso (es decir, sin soltar y crear o restablecer secuencias), o incluso cualquier trabajo, podría considerar algo como esto (esto es solo en principio, no he probado este enfoque, pero estoy seguro que funcionará):
-
Crea una sola secuencia.
-
Cree una tabla de referencia, con una fila para cada año, p. Ej.
AÑOS (año NÚMERO (4,0) LLAVE PRINCIPAL, valor_inicial NÚMERO)
-
Cuando tengas
NEXTVALde la secuencia, luego tienes que restar elstarting_valuecuando se consulta desde la tabla AÑOS para el año actual. Si no se encuentra el año, se debe insertar una nueva fila (es decir, el primer proceso ejecutado en un año determinado insertará el nuevo valor).
p. ej., una función, p. ej. get_year_starting_value (pn_year IN NUMBER) RETURN NUMBER podría consultar esta tabla y devolver el starting_value para el año dado; si se pone NO_DATA_FOUND, podría llamar a un procedimiento para insertarlo usando el NEXTVAL de la secuencia (comprometida en una transacción autónoma para que el nuevo valor esté disponible inmediatamente para otras sesiones y para que la función no falle debido al efecto secundario)
Probablemente no sea una solución para todos los casos, pero creo que este enfoque puede ayudar al menos en algunos escenarios.
Utilice un trabajo para hacer el truco. Primero, cree un procedimiento almacenado para restablecer su secuencia (generalmente uso la solución DROP / CREATE, pero podría usar el truco de spencer7593):
CREATE OR REPLACE PROCEDURE my_seq_reset AS
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE my_seq';
EXECUTE IMMEDIATE
'CREATE SEQUENCE my_seq' ||
' MINVALUE 1 ' ||
' MAXVALUE 999999 ' ||
' START WITH 1 ' ||
' INCREMENT BY 1 ' ||
' NOCACHE';
END;
Luego cree el trabajo (vea aquí para la referencia):
BEGIN
dbms_scheduler.create_job(
job_name => 'job$my_seq_reset',
job_type => 'STORED_PROCEDURE',
job_action => 'my_seq_reset',
start_date => TO_DATE('01-01-09', 'DD-MM-RR'),
repeat_interval => 'FREQ=YEARLY;BYDATE=0101',
enabled => TRUE,
auto_drop => FALSE,
comments => 'My sequence yearly reset job.'
);
END;
Ya terminaste.