Como resetear y como obtener el próximo valor de una secuencia

Varias veces me preguntaron como reiniciar (resetear) una secuencia de modo de que comience a generar valores desde el principio nuevamente. Esto suele ser util en la etapa de testing de aplicativos ya que en las pruebas se necesita probar desde cero varias veces. Oracle, al menos hasta ahora, no provee un forma nativa de reiniciar una secuencia (por ejemplo algo como alter sequence reset) pero si permite cambiar el incremento, que como default es 1, y setearlo a un valor negativo, por lo tanto en el próximo nextval va a restar (decrementar) el valor. Si hacemos un decremento que logre que pase de su valor actual a 1 conseguiriamos hacer que el contador se resetee. Les voy a mostrar como hacerlo creando un stored procedure:

rop@DESA10G> create sequence seq;

Secuencia creada.

rop@DESA10G> create or replace procedure reset_seq( p_seq_name in varchar2 ) is
 2           l_val number;
 3      begin
 4           execute immediate
 5           'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 6           execute immediate
 7          'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';
 8           execute immediate
 9          'select ' || p_seq_name || '.nextval from dual' INTO l_val;
10           execute immediate
11          'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
12      end;
13  /

Procedimiento creado.

Ahora voy a incrementar el valor de la secuencia varias veces:

rop@DESA10G> select seq.nextval from dual;

  NEXTVAL
----------
        1

rop@DESA10G> select seq.nextval from dual;

  NEXTVAL
----------
        2

rop@DESA10G> /

  NEXTVAL
----------
        3

rop@DESA10G> /

  NEXTVAL
----------
        4

rop@DESA10G> /

  NEXTVAL
----------
        5

rop@DESA10G> /

  NEXTVAL
----------
        6

Si hicieramos nextval el próximo valor será el 7. Ahora vamos a reiniciar la secuencia usando el procedimiento creado mas arriba.

rop@DESA10G> exec reset_seq('SEQ');

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select seq.nextval from dual;

  NEXTVAL
----------
        1

rop@DESA10G> /

  NEXTVAL
----------
        2

Como observamos en el ejemplo, la secuencia comenzó a generar valores nuevamente.


Ahora voy a mostrarles como obtener el próximo valor de una secuencia. En principio uno piensa en obtener ese dato desde una vista de catalogo, pero si la sequencia utiliza cache mayor a 0 (el default es 20) no nos va mostrar el próximo valor real sino que nos va a mostrar el próximo no cacheado. Para ser mas claro les voy a mostrar un ejemplo

 
rop@DESA10G> create sequence seq;

Secuencia creada.

rop@DESA10G> select seq.nextval from dual;

  NEXTVAL
----------
        1

rop@DESA10G> /

  NEXTVAL
----------
        2

rop@DESA10G> /

  NEXTVAL
----------
        3

rop@DESA10G> /

  NEXTVAL
----------
        4

rop@DESA10G> /

  NEXTVAL
----------
        5


rop@DESA10G> select * from user_sequences where sequence_name = 'SEQ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SEQ                                     0 1.0000E+27            1 N N         20          21

1 filas seleccionadas.

Consultando la vista USER_SEQUENCES vemos que la columna last_number podría servirnos para saber cual será el próximo valor, pero vemos que el valor es 21 y el próximo valor deberia ser 6, entonces no nos sirve esa vista para lo que queremos. Para poder conocer exactamente el valor siguiente que nos entregará una secuencia tenemos que usar v$_sequences que no es visible desde ningun usuario y no tiene sinonimo publico. Lo que voy a hacer es presentar esa vista a todos los usuarios para que se pueda consultar:

sys@ROP10G> desc v$_sequences
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------
SEQUENCE_OWNER                                                 VARCHAR2(64)
SEQUENCE_NAME                                                  VARCHAR2(1000)
OBJECT#                                                        NUMBER
ACTIVE_FLAG                                                    VARCHAR2(1)
REPLENISH_FLAG                                                 VARCHAR2(1)
WRAP_FLAG                                                      VARCHAR2(1)
NEXTVALUE                                                      NUMBER
MIN_VALUE                                                      NUMBER
MAX_VALUE                                                      NUMBER
INCREMENT_BY                                                   NUMBER
CYCLE_FLAG                                                     VARCHAR2(1)
ORDER_FLAG                                                     VARCHAR2(1)
CACHE_SIZE                                                     NUMBER
HIGHWATER                                                      NUMBER
BACKGROUND_INSTANCE_LOCK                                       VARCHAR2(1)
INSTANCE_LOCK_FLAGS                                            NUMBER

sys@ROP10G> create view v$sequences as select * from v$_sequences;

View created.

sys@ROP10G> create public synonym v$sequences for sys.v$sequences;

Synonym created.

sys@ROP10G> grant select on v$sequences to public;

Grant succeeded.

Ahora que ya publicamos la vista podemos hacer la siguiente consulta para obtener el próximo valor:

rop@DESA10G> ed
Escrito file afiedt.buf

 1  select b.object_name,a.nextvalue
 2  from v$sequences a, user_objects b
 3  where a.object# = b.object_id and
 4*       b.object_name = 'SEQ'
rop@DESA10G> /

OBJECT_NAME        NEXTVALUE
----------------------------
SEQ                  6

Como se observa, el valor retornado fue lo que esperabamos. Ahora verificamos haciendo un nextval:

rop@DESA10G> select seq.nextval from dual;

  NEXTVAL
----------
        6

rop@DESA10G>

Efectivamente el próximo valor era 6. No se si esto pueda tener gran utilidad pero la idea era mostrarles como hacerlo.