Problème avec sequence sous 11g

Version imprimable

Bonjour,
Lorsque j'exécute le script suivant (tout bête !), j'obtiens comme chiffre pour mes n° de membre 2 et 3, alors que je devrais obtenir 1 puis 2. J'ai ce problème sous 11g et 11gR2, mais par contre, cela fonctionnait très bien sous 10g. Quelqu'un a-t-il une idée ?
Merci.

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
drop table MEMBRE cascade constraints;
 
/*==============================================================*/
/* Table : MEMBRE                                               */
/*==============================================================*/
create table MEMBRE 
(
   NUM_MEMBRE           NUMBER(6)            not null,
   NOM                  VARCHAR2(80)         not null,
   PRENOM               VARCHAR2(80)         not null,
   RUE                  VARCHAR2(100)        not null,
   CP                   CHAR(5)              not null,
   VILLE                VARCHAR2(50)         not null,
   TELEPHONE            CHAR(10),
   ADHESION             DATE                 not null,
   DUREE                NUMBER(2)            not null,
   constraint PK_MEMBRE primary key (NUM_MEMBRE),
   constraint CK_MEMBRES_DUREE check (DUREE>0)
);
 
drop sequence MEMBRE_SEQ;
Create sequence MEMBRE_SEQ;
 
Insert into MEMBRE (NUM_MEMBRE, NOM, PRENOM, RUE, CP, VILLE, TELEPHONE, ADHESION, DUREE) values (MEMBRE_SEQ.nextval, 'ALBERT', 'Marc', '13 rue des alpes', '69008', 'Lyon', '0601020304', sysdate-60, 1);
Insert into MEMBRE (NUM_MEMBRE, NOM, PRENOM, RUE, CP, VILLE, TELEPHONE, ADHESION, DUREE) values (MEMBRE_SEQ.nextval, 'BERNAUDET', 'Barnabé', '6 rue des bécasses', '69007', 'Lyon', '0602030105', sysdate-10, 3);
COMMIT;
 
select * from membre;

02/10/2011, 09h59
ORA-007

Bonjour,

http://download.oracle.com/docs/cd/B...ments_6015.htm

Quand tu créés une séquence la première valeur par défaut est 1.

Dans ton premier insert on a

Code:

MEMBRE_SEQ.NEXTVAL

soit 2.

En effet, c'est un problème réel et répertorié, effet secondaire désagréable de la création de segment différée. Ce mécanisme permet de créer une table sans allouer d'espace (c'est à dire sans créer de segment) tant qu'on n'insère pas au moins une ligne.

Ce problème troublant survient lorsque les conditions suivantes sont cumulées :
- Oracle 11gR2
- édition Entreprise
- deferred_segment_creation=true
- table vide
- séquence non initialisée (encore jamais appelée)
- schéma autre que SYS ou SYSTEM

Dans ces conditions, lorsque vous insérez la première ligne de la table tout en faisant appel à une séquence "toute neuve", celle-ci subit une double incrémentation.

A la ligne 39 de cet exemple, on voit que la séquence, appelée pour la première fois, renvoie 2 au lieu de 1.
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 SYSTEM@ORA112>show parameter defer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SYSTEM@ORA112>drop table scott.test purge; Table supprimée. SYSTEM@ORA112>drop sequence seqtest; Séquence supprimée. SYSTEM@ORA112>create sequence seqtest start with 1 increment by 1; Séquence créée. SYSTEM@ORA112>create table scott.test(a int); Table créée. SYSTEM@ORA112>select segment_name, segment_type 2 from dba_segments 3 where owner='SCOTT' 4 and segment_name='TEST'; --> aucun segment n'est associé à cette table aucune ligne sélectionnée SYSTEM@ORA112>insert into SCOTT.test values(seqtest.nextval); 1 ligne créée. SYSTEM@ORA112>select * from test; A ---------- 2 SYSTEM@ORA112>select segment_name, segment_type 2 from dba_segments 3 where owner='SCOTT' 4 and segment_name='TEST'; --> il y a maintenant un segment SEGMENT_NAME SEGMENT_TYPE ------------------------------- --------------- TEST TABLE
La note Metalink 1050193.1 décrit ce phénomène, et a le culot de considérer que c'est un comportement normal (alors que c'était initialement considéré comme une bogue de sévérité 2), en arguant qu'une séquence n'a pas vocation à produire des numéros forcément prévisibles.

Pour éviter tout souci, il faut désactiver la création de segment différée.

Cela peut se faire au niveau de la table par exemple :
Code:

1 2 create table test(a int) segment creation immediate;
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 SYSTEM@ORA112>show parameter defer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SYSTEM@ORA112>drop table scott.test purge; Table supprimée. SYSTEM@ORA112>drop sequence seqtest; Séquence supprimée. SYSTEM@ORA112>create sequence seqtest; Séquence créée. SYSTEM@ORA112> SYSTEM@ORA112>create table scott.test(a int) 2 segment creation immediate; Table créée. SYSTEM@ORA112>select segment_name, segment_type 2 from dba_segments 3 where owner='SCOTT' 4 and segment_name='TEST'; SEGMENT_NAME SEGMENT_TYPE ----------------------- ------------------ TEST TABLE SYSTEM@ORA112>insert into SCOTT.test values(seqtest.nextval); 1 ligne créée. SYSTEM@ORA112>select * from scott.test; A ---------- 1

pourquoi ne pas créer cette sequence comme:

Code:

1
2
3
4
5
6
create sequence MEMBRE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

03/10/2011, 11h00
mnitu

Citation:

Envoyé par Pomalaix

...La note Metalink 1050193.1 décrit ce phénomène, et a le culot de considérer que c'est un comportement normal (alors que c'était initialement considéré comme une bogue de sévérité 2), en arguant qu'une séquence n'a pas vocation à produire des numéros forcément prévisibles.
...

Personnellement, je partage en partie leur point de vue.
03/10/2011, 11h45
Waldar

C'est vrai que le bug est surprenant et je n'irai pas dire que c'est un comportement normal, mais ce n'est pas plus grave qu'un "trou" créé suite à un rollback.
03/10/2011, 11h49
Pomalaix

Citation:

Envoyé par mnitu

Personnellement, je partage en partie leur point de vue.

Sur ce coup-là, ils sont franchement de mauvaise foi chez Oracle.
La création de segment différée provoque une petite régression qui leur avait échappé, ils feraient mieux de corriger leur code pour que les choses continuent à fonctionner comme avant, plutôt que de trouver un prétexte digne du pire des politiciens pour justifier cette anomalie.

Une séquence n'a pas à fournir des valeurs prévisibles ?
A quoi sert d'avoir une clause START WITH alors ?
A quoi sert de préciser INCREMENT BY ?

Oracle est déjà un produit assez compliqué comme ça avec ses exceptions de comportement dans tous les sens, sans avoir besoin d'en rajouter...
03/10/2011, 12h00
mnitu

Cher Pomalaix,
Vous avez sans aucun doute raison. Mais, je pense également que l’utilisation correcte d’une séquence n’impose en rien la présence obligatoire d’une valeur bien précisée comme 1 dans ce cas. Donc ce n’est pas joli mais ce n’est pas grave non plus.
De plus ça va faire une « bonne question d’expert » :lol: d’ici peu de temps :
Le paramétrage de base est …
Je crée une séquence comme suit …
Je l’utilise comme suit …
Quelle est la valeur insérée dans la table : 1 ou 2 ?
03/10/2011, 12h05
Pomalaix

Citation:

Envoyé par Waldar

...mais ce n'est pas plus grave qu'un "trou" créé suite à un rollback.

Si c'est plus grave, car ça change le comportement qu'on connaissait jusque là. Une séquence est censée fournir des numéros à partir du START WITH, en respectant INCREMENT BY, et ce
sans trou sous réserve qu'on ne fasse pas de ROLLBACK ou que l'instance ne soit pas redémarrée.

Je connais plus d'un script d'initialisation d'environnement basé sur ces hypothèses. Si on a dit START WITH 1, ils s'attendent à trouver 1 comme première valeur.
J'avais concrètement rencontré ce cas en début d'année dernière, où ce décalage initial de la séquence fichait en l'air la validité des clés étrangères.

Alors bien sûr, on peut objecter que ces scripts étaient critiquables, mais le fait est que ce qui marchait avant ne marchait plus, et ce sans raison valable.
03/10/2011, 15h06
mnitu

Citation:

Envoyé par Pomalaix

....J'avais concrètement rencontré ce cas en début d'année dernière, où ce décalage initial de la séquence fichait en l'air la validité des clés étrangères.

Alors bien sûr, on peut objecter que ces scripts étaient critiquables, mais le fait est que ce qui marchait avant ne marchait plus, et ce sans raison valable.

Voir carrément erronés!
03/10/2011, 15h24
Pomalaix

Citation:

Envoyé par mnitu

Voir carrément erronés!

Arf, j'aurais parié ma paye que tu rebondirais là-dessus :mouarf:
03/10/2011, 17h13
mnitu

Citation:

Envoyé par Pomalaix

Arf, j'aurais parié ma paye que tu rebondirais là-dessus :mouarf:

J’aime le volley et donc quand on me lève le ballon ... c’est plus fort que moi. :oops:
05/10/2011, 16h53
vince7401
Bonjour,
Merci beaucoup à Pomalaix pour la réponse.
Je trouve également qu'il s'agit d'un bug.
D'autant plus que si je supprime les données de la table puis si je supprime la vue et la recréé, le nombre démarre bien à un (et non à 2) !
J'ajoute que le code suivant ne change rien à l'erreur :
Code:

1 2 3 4 5 6 CREATE sequence MEMBRE_SEQ minvalue 1 maxvalue 999999999999999999999999999 start WITH 1 increment BY 1 cache 20;
Ceci dit, ce qui m'arrangerait, ce serait d'avoir un comportement par défaut de création immédiate de segment, sans avoir à ajouter "segment creation immediate" dans chaque CREATE TABLE. Y a-t-il un paramètre dans Oracle qui me permette de le faire ?
Merci.
05/10/2011, 18h02
skuatamad

Pomalaix l'a montré en 1ere ligne de sa démo :
DEFERRED_SEGMENT_CREATION
06/10/2011, 22h00
pachot

Bonjour,

Citation:

Envoyé par mnitu

Personnellement, je partage en partie leur point de vue.

Pour en rajouter un peu, je dirais que l'erreur de départ, c'est d'avoir fait des sequences qui
1) s'appellent sequences
2) donnent un NUMBER
On aurait un 'generator' qui donnerait seulement un RAW en garantissant seulement de ne pas tomber 2 fois sur le même, et personne ne se plaindrait des trous :D
Codialement,
Franck.
07/10/2011, 00h36
Waldar

Comme un sys_guid() ?
07/10/2011, 08h06
pachot

Citation:

Envoyé par Waldar

Comme un sys_guid() ?

Sauf qu'il ferait moins de 16 bytes, et qu'il n'irait qu'en s'incrémentant pour optimiser les index.
07/10/2011, 08h42
mnitu

Citation:

Envoyé par pachot

...
On aurait un 'generator' qui donnerait seulement un RAW en garantissant seulement de ne pas tomber 2 fois sur le même, et personne ne se plaindrait des trous :D

Mais non c’est pire que ça : la machine s’en fout de trous c’est l’humain qu’en a besoin, en gros pour faire comme avant… !
07/10/2011, 08h49
laurentschneider

1) les séquences
c'est une bizarrerie Oracle qui remplace mal une colonne "auto-increment" dans MS Access. c'est en général beaucoup plus rapide que toute autre soluce sys_guid ou autre

2) deferred segment creation
plein d'effets secondaires et de trucs non testés. L'éternelle complainte des dbas lorsqu'on bousille leurs scripts

http://laurentschneider.com/wordpres..._segments.html
07/10/2011, 10h21
Pomalaix

Citation:

Envoyé par laurentschneider

L'éternelle complainte des dbas lorsqu'on bousille leurs scriptsl

Tout à fait ! La création de segment différée est une riche idée dans le principe, mais il semble que ses conséquences n'ont été que maigrement évaluées, et en tout cas pas prises en compte.

J'aurais bien vu une colonne supplémentaire DEFERRED=YES/NO dans DBA_SEGMENTS, mais il est probable qu'il aurait fallu quand même modifier ses scripts pour traiter les autres colonnes ayant une valeur bizarre, quand le segment n'est pas matérialisé.