Script de modification de séquences existantes

Version imprimable

Bonjour,

je voulais savoir si qq'un avait un script qui permettrait de dropper des sequences existantes appartenant à un user et de les recréer en modifiant la valeur du startwith de la nouvelle sequence avec le next_val de la sequence existante.
Si on pouvait faire un ALTER SEQUENCE pour modifier le start with ça serait bien mais on ne peut pas.

voici un début de script que j'ai élaboré mais il me manque les autres caractéristiques de la séquence:cycle,order etc.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 declare v_sql varchar2(255); v_num_seq number; begin -- boucle sur toutes les sequences for cur_rec in (select * from user_sequences) loop -- récup du nextval de la séquence v_sql := 'select ' || cur_rec.sequence_name || '.nextval from dual'; execute immediate v_sql into v_num_seq; v_sql := 'DROP SEQUENCE ' || cur_rec.sequence_name; execute immediate v_sql; v_sql := 'create sequence '|| cur_rec.sequence_name || ' minvalue ' || cur_rec.min_value || ' maxvalue ' || cur_rec.max_value || ' increment by ' || cur_rec.increment_by || ' START WITH ' || v_num_seq; execute immediate v_sql; end loop; end; /

qu'en pensez-vous ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
declare
 
	v_sql varchar2(255);
	v_num_seq number;
 
begin
 
	-- boucle sur toutes les sequences
	for cur_rec in (select * from user_sequences)
	loop
 
		-- récup du nextval de la séquence
		v_sql := 'select ' || cur_rec.sequence_name || '.nextval from dual';
		execute immediate v_sql into v_num_seq;
 
		-- suppression de la sequence
		v_sql := 'DROP SEQUENCE ' || cur_rec.sequence_name;
		execute immediate v_sql;
 
		-- on recréer la même séquence en modifiant juste le start with
		v_sql := 'create sequence '|| cur_rec.sequence_name || ' minvalue ' || cur_rec.min_value || ' maxvalue ' || cur_rec.max_value || 
			' increment by ' || cur_rec.increment_by || ' START WITH ' || v_num_seq;
 
		-- si CYCLE est activé sinon NOCYCLE par defaut
		IF cur_rec.cycle_flag = 'Y' THEN
			v_sql := v_sql || ' CYCLE';
		END IF;
 
		-- si CACHE = 0 alors NOCACHE si 20 alors valeur par defaut
		IF cur_rec.cache_size = '0' THEN
			v_sql := v_sql || ' NOCACHE';
		ELSE
			v_sql := v_sql || ' CACHE ' || cur_rec.cache_size;
		END IF;
 
		-- si ORDER est activé sinon NOORDER par defaut
		IF cur_rec.order_flag = 'Y' THEN
			v_sql := v_sql || ' ORDER';
		END IF;		
 
		execute immediate v_sql;
 
	end loop;
 
end;
/

faut il penser à autre chose?
les synonymes existant sur ces sequences normalement ne bougent pas ?

21/08/2009, 15h39
Pomalaix

Citation:

Envoyé par farenheiit

je voulais savoir si qq'un avait un script qui permettrait de dropper des sequences existantes appartenant à un user et de les recréer en modifiant la valeur du startwith de la nouvelle sequence avec le next_val de la sequence existante.
Si on pouvait faire un ALTER SEQUENCE pour modifier le start with ça serait bien mais on ne peut pas.

Bonjour

Ca donne l'impression de ne servir strictement à rien ! Quel est l'intérêt de la manip ??
21/08/2009, 15h43
farenheiit

si ça sert car des sequences ont été recrées sur une base existante avec des valeurs de start with incorrects: certaines valeurs de séquences étaient déjà présentes en base sur des colonnes uniques.

il me faut recréer les séquences avec les bonnes valeurs pour le start with.

il me semble que mon script est bon finalement.

je me demande juste s'il peut y avoir des impacts sur le reste de la base.
Il est certain que les synonymes sur ces sequences seront invalides; il faudra donc les recompiler.

cdmt,
21/08/2009, 16h10
Severn

Pomalaix a raison, ce script ne servira à rien au vu de votre problème de 'start with'.

Puisque vos séquences sont utilisées pour alimenter certaines colonnes de vos tables, il faut que votre script aille lire les valeurs courantes de ces colonnes pour les utiliser comme valeurs de départ (+1) de vos séquences. Ce qu'on ne voit pas à la lecture de votre script.
21/08/2009, 16h48
farenheiit

je suis trop bête!!!
21/08/2009, 16h56
farenheiit

il faut en fait que je récupère les valeurs des sequences de la base prod.
comment modifier le script pour qu'il me génère un script à partir d'une base de prod pour que je puisse l'executer sur la base de test
21/08/2009, 17h15
Severn

Si vos séquences en base de prod sont correctes, alors vous pouvez utiliser votre script pour produire les commandes que vous allez lancer en environnement de test. Donc au lieu de faire des execute immediate, utilisez dbms_output.put_line pour générer un fichier à utiliser en test.

Seul bémol toutefois, vos valeurs en base de test ne doivent pas avoir dépassées celles en prod. Rien n'est moins sûr car des batteries de tests peuvent parfois modifier sérieusement les données en base. Vérifiez cela avant de lancer votre script, autrement vous êtez bon pour rechercher le max des valeurs de chaque colonne associée à vos séquences.

ce script marche:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
set serveroutput on
spool maj_sequences_genere.sql
 
declare
 
	v_sql varchar2(255);
	v_num_seq number;
 
begin
 
	-- boucle sur toutes les sequences
	for cur_rec in (select * from user_sequences)
	loop
 
		-- récup du nextval de la séquence
		v_sql := 'select ' || cur_rec.sequence_name || '.nextval from dual';
		execute immediate v_sql into v_num_seq;
 
		-- suppression de la sequence
		v_sql := 'DROP SEQUENCE ' || cur_rec.sequence_name || ';';
		dbms_output.put_line(v_sql);
 
		-- on recréer la même séquence en modifiant juste le start with
		v_sql := 'create sequence '|| cur_rec.sequence_name || ' minvalue ' || cur_rec.min_value || ' maxvalue ' || cur_rec.max_value || 
			' increment by ' || cur_rec.increment_by || ' START WITH ' || v_num_seq;
 
		-- si CYCLE est activé sinon NOCYCLE par defaut
		IF cur_rec.cycle_flag = 'Y' THEN
			v_sql := v_sql || ' CYCLE';
		END IF;
 
		-- si CACHE = 0 alors NOCACHE si 20 alors valeur par defaut
		IF cur_rec.cache_size = '0' THEN
			v_sql := v_sql || ' NOCACHE';
		ELSE
			v_sql := v_sql || ' CACHE ' || cur_rec.cache_size;
		END IF;
 
		-- si ORDER est activé sinon NOORDER par defaut
		IF cur_rec.order_flag = 'Y' THEN
			v_sql := v_sql || ' ORDER';
		END IF;		
 
 
		dbms_output.put_line(v_sql||';');
 
		-- on donne les droits en select sur cette sequence
		v_sql := 'grant select on ' || cur_rec.sequence_name || ' to public;';
		dbms_output.put_line(v_sql);
 
 
	end loop;
 
end;
/
set serveroutput off

par contre à la fin du fichier spoolé j'ai les lignes suivantes:

Code:

1
2
3
4
 
Procédure PL/SQL terminée avec succès.
SQL> 
SQL>

comment faire pour ne pas qu'elles apparaissent ?

21/08/2009, 18h03
Severn

set feedback off
21/08/2009, 18h06
farenheiit

ça marche super.

par contre j'ai dû aussi rajouter le grant select sur les sequences car si les synonymes restent lors de la suppression des sequences, les privilèges s'en vont.