Problème avec sequence sous 11g

[vince7401]

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;

[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.

[Pomalaix]

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

[boussafi]

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;

[mnitu]

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.

[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.

[Pomalaix]

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...

[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 » 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 ?

[Pomalaix]

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.

[mnitu]

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!

[Pomalaix]

Envoyé par mnitu

Voir carrément erronés!

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

[mnitu]

Envoyé par Pomalaix

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

J’aime le volley et donc quand on me lève le ballon ... c’est plus fort que moi.

[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.

[skuatamad]

Pomalaix l'a montré en 1ere ligne de sa démo :
DEFERRED_SEGMENT_CREATION

[pachot]

Bonjour,

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
Codialement,
Franck.

[Waldar]

Comme un sys_guid() ?

[pachot]

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.

[mnitu]

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

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… !

[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

[Pomalaix]

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é.