Seqüències d'Oracle (2 de 2)

Ja vam comentar què eren les seqüències d'Oracle i els problemes que podien donar, així que avui mirarem de veure què podem fer per sobreviure-hi.

Els principals problemes de les SEQUENCES són que no estan directament associades a una taula i que pot ser que generin números que ja existeixen i donaran errors de clau duplicada.

Com a tot, el millor és adquirir bones costums, que això evita molts mal de caps.

El millor, en la meva opinió, és tenir una seqüència diferent per cada taula que necessiti, i que siguin fàcilment identificables. A mi em resulta molt útil associar a cada taula un nom curt entre 2 i 4 lletres, i anotar aquestes associacions a alguna banda. Després els índexs, les restriccions, les claus i tot el que tingui a veure amb la taula, tindrà aquest nom curt com a part del nom. Així tot és fàcilment identificable.

Per exemple, si tenim les taules EMPLEATS i DEPARTAMENTS, els hi donarem els noms curts EMP i DEP, i les seves seqüències associades seran SEQ_EMP i SEQ_DEP.

Aquesta associació la podem mantenir a una taula de metadades MET_TABLES(table_name, short_name), per exemple. Així fem nosaltres la feina que no ens fa l'Oracle automàticament.

A una anterior empresa vaig muntar un script (despendoleitor.sql, li dèiem) que omplia aquesta taula de metadades amb dos columnes més: max_value i current_seq. La primera columna l'omplia amb el SELECT MAX(*) per cada taula i la segona amb el valor que retornaria un NEXTVAL de la seqüència associada (obtingut directament des de USER_SEQUENCES).

Amb aquestes dades farcides el que fèiem era detectar si havia seqüències amb valors per sota del màxim i, en aquests casos, fèiem un DROP i un CREATE de la SEQUENCE amb un valor per sobre del màxim.

Tot això està molt bé però esborrar i recrear les seqüències fa que quedin invàlids tots els PL/SQL que les fan servir, i recompilar-los pot suposar una estoneta.

La manera d'evitar-ho consisteix en fer un ALTER de la seqüència per modificar l'INCREMENT BY, fer-li un NEXTVAL, i restaurar-li l'INCREMENT BY que tenia. També desactivem el CACHE abans de fer el NEXTVAL i desprès el restaurem, però això no seria tan important.

Per tal d'aconseguir-ho hem fet un petit procediment PL/SQL i aquí el teniu:

CREATE OR REPLACE PROCEDURE sequence_updater(
   p_owner IN VARCHAR2
  ,p_sequence_name IN VARCHAR2
  ,p_last_number IN NUMBER
)
IS
  v_last_number NUMBER;
  v_increment_by NUMBER;
  v_cache_size NUMBER;
  v_new_increment NUMBER;
  v_last_value NUMBER;
  v_aux NUMBER;
BEGIN
  SELECT last_number, increment_by, cache_size
  INTO v_last_number, v_increment_by, v_cache_size
  FROM all_sequences
  WHERE sequence_owner = p_owner
    AND sequence_name = p_sequence_name;

  v_new_increment := p_last_number - v_last_number;

  IF v_new_increment != 0 THEN
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| p_owner ||'.'||p_sequence_name
      || ' INCREMENT BY '|| v_new_increment ||' NOCACHE';
    EXECUTE IMMEDIATE 'SELECT '|| p_owner ||'.'
      || p_sequence_name ||'.nextval FROM DUAL' INTO v_aux;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| p_owner ||'.'||p_sequence_name
      || ' INCREMENT BY '||v_increment_by ||' NOCACHE';

    LOOP
      EXECUTE IMMEDIATE 'SELECT '|| p_owner ||'.'
        || p_sequence_name ||'.currval FROM DUAL' INTO v_last_value;
      EXIT WHEN v_last_value >= (p_last_number - 1);
      EXECUTE IMMEDIATE 'SELECT '|| p_owner ||'.'
        || p_sequence_name ||'.nextval FROM DUAL' INTO v_last_value;
    END LOOP;

    IF v_cache_size != 0 THEN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| p_owner ||'.'
        ||p_sequence_name || ' CACHE '|| v_cache_size;
    ELSE
      EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| p_owner ||'.'
        ||p_sequence_name || ' NOCACHE';
    END IF;
  END IF;

END sequence_updater;
/

Vam intentar posar aquest codi en un esquema que fos DBA i actualitzar totes les seqüències de tots els esquemes, però no ens funcionava i no sabem per què. Només ens ha funcionat si cada esquema té el procediment. Crec recordar que al PL/SQL existeix la manera de crear procediments que s'executin amb els drets del propietari o amb els drets del que l'executa. Potser podríem investigar per aquí. De moment sí que funciona si el crida el propietari.

La crida és una cosa tan senzilla com:

EXEC SEQUENCE_UPDATER('NOM_DE_TAULA', 'TAB_SEQ', 3824);

Després d'una exportació d'una base de dades en execució es pot obtenir el valor de totes les seqüències i preparar un script que cridi al SEQUENCE_UPDATER. Imagino que amb el data pump també es pot fer una exportació de només les seqüències, però crec que serà més ràpid l'execució del script.

Després de muntar el nostre script vam trobar un altre script que feia el mateix a la web de PSOUG. En aquest cas li diuen reset_sequence (està cap al final de la pàgina).

Amb aquest nou procediment i aquestes bones pràctiques s'hauran acabat molts mal de caps amb les seqüències de l'Oracle.

Primera part: Seqüències d'Oracle (1 de 2)