Il vous est certainement arrivé d’avoir a réinitialiser la valeur d’une séquence. si cette opération ne semble pas compliquée pour une ou deux séquences, elle peut vite devenir fastidieuse dans le cas de quelques dizaines.
Je vous propose la solution suivante, dont le seul prérequis est que chaque séquence ne doit alimenter qu’une seule table.
Dans un premier temps, on crée un fichier au format CSV avec les informations suivantes :
Nom du schéma;Nom de la table;Nom de la clef;Nom de la séquence
On crée en suite une directory Oracle avec la localisation de ce fichier
create or replace directory EXT_TABLE_DIR as '/home/oracle/external_tables' ;
On crée une table externe mappant ce fichier, le but étant de mettre les données du fichier à disposition, comme une table Oracle.
create table ext_reinit_seq
(
OBJ_OWNER VARCHAR2(32),
TABLE_NAME VARCHAR2(32),
KEY_NAME VARCHAR2(32),
SEQ_NAME VARCHAR2(32)
)
organization external
(
type ORACLE_LOADER
default directory EXT_TABLE_DIR
access parameters
(
records delimited by NEWLINE
badfile EXT_TABLE_DIR:'reinit_seq%a%p.bad'
logfile EXT_TABLE_DIR:'reinit_seq%a%p.log'
fields terminated by ';'
missing field values are null
(
OBJ_OWNER ,
TABLE_NAME ,
KEY_NAME ,
SEQ_NAME
)
)
LOCATION ('reinit_sequence.csv')
)
REJECT LIMIT UNLIMITED;
On flushe la mémoire pour vider les numéros de séquences déjà en mémoire
alter system flush SHARED_POOL; alter system flush BUFFER_CACHE;
On positionne le spool
set serveroutputon size unlimited
On peut maintenant lancer la procédure suivante :
declare
newvalue NUMBER;
Current_value NUMBER;
ln NUMBER;
ib NUMBER;
MinVal Number;
requete_sql VARCHAR2(4000);
BEGIN
for ligne in (select OBJ_OWNER,TABLE_NAME,KEY_NAME,SEQ_NAME from ext_reinit_seq)
loop
SELECT last_number,
MIN_VALUE ,
increment_by
INTO ln,
Minval ,
ib
FROM dba_sequences
WHERE sequence_owner = upper(ligne.OBJ_OWNER)
AND sequence_name = upper(ligne.SEQ_NAME);
Requete_Sql := 'select max('||ligne.KEY_NAME||') from ' || ligne.OBJ_OWNER || '.' || ligne.TABLE_NAME;
execute immediate Requete_Sql into newvalue ;
DBMS_OUTPUT.PUT_LINE('SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME||' MAX :'|| newvalue || ' Last Number ' || ln || 'Min Val '|| Minval);
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' ORDER';
if ( newvalue != ln and newvalue - ln >= Minval )
-- if ( newvalue != ln and newvalue >= Minval )
-- if ( newvalue >= Minval )
then
DBMS_OUTPUT.PUT_LINE('ALTER SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' INCREMENT BY ' || (newvalue - ln));
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' INCREMENT BY ' || (newvalue - ln);
EXECUTE IMMEDIATE 'SELECT ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO ln;
-- DBMS_OUTPUT.PUT_LINE('SELECT ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME ||'.NEXTVAL FROM DUAL');
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' INCREMENT BY ' || ib;
-- DBMS_OUTPUT.PUT_LINE('ALTER SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' INCREMENT BY ' || ib);
end if;
EXECUTE IMMEDIATE 'SELECT ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO ln;
Requete_Sql := 'select last_number from dba_sequences WHERE sequence_owner = upper(''' || ligne.OBJ_OWNER|| ''') AND sequence_name = upper('''||ligne.SEQ_NAME||''')';
-- DBMS_OUTPUT.PUT_LINE(Requete_Sql);
execute immediate Requete_Sql into Current_value ;
DBMS_OUTPUT.PUT_LINE('SEQUENCE ' || ligne.OBJ_OWNER || '.' || ligne.SEQ_NAME || ' valeur courante :' || Current_value );
DBMS_OUTPUT.PUT_LINE('=================================================================================================' );
end loop;
commit;
END;
/
Voilà, c’est fait ! Et vous disposez de la liste des séquences mises a jour avec le nouveau numéro courant.
