Discussion :

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

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
create table test(a int)
segment creation immediate;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[islamov2000]

pourquoi ne pas créer cette sequence comme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[mnitu]

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

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

[Pomalaix]

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 ?

[pachot]

Bonjour,

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() ?

[mnitu]

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