Migration Oracle 11 : Problème table mutante

[callenou]

Bonjour,

Je rencontre un problème que je n’arrive pas à comprendre suite à la migration d’oracle 10.2.0.4 en 11.2.0.2.
j'ai un traitement que j'ai volontairement simplifié qui insère dans une table TABLE_TEST_CA des lignes qui sont sélectionnées via une fonction F_TEST_CA.

F_TEST_CA sélectionne les lignes dans la table TABLE_TEST_CA et les renvoie par une table type T_TEST_CA_TABLE.
A noter qu’il n’y a aucun trigger, aucune contrainte sur TABLE_TEST_CA.

Je fais ensuite l’insertion :

Code :

1
2
3
4
5
6
7
INSERT INTO TABLE_TEST_CA
   (child_code    ,
    mother_code
    )
SELECT child_code  ,
          mother_code      
FROM TABLE(F_TEST_CA);

A l’exécution, j'ai le message d’erreur suivant : ORA-04091 : la table TABLE_TEST_CA est en mutation; le déclencheur ou la fonction ne peut la voir.

Le même traitement ne plante pas sur notre base en oracle 10.2.0.4.

Je cherche à savoir quel est le problème. Si vous avez rencontré ce style de problème lors de la migration 11G, je suis intéressée par vos pistes !

Ci-dessous les scripts utilisés pour mon test que vous pouvez exécuter :

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
/*drop table TABLE_TEST_CA
drop type T_TEST_CA_TABLE
drop type T_TEST_CA 
drop function F_TEST_CA*/
 
CREATE TABLE TABLE_TEST_CA
(
  child_code       varchar2(50 byte),
  mother_code   varchar2(50 byte)
)
 
INSERT INTO TABLE_TEST_CA VALUES ( '0001015','0253640')
 
-- Definition of type T_TEST_CA
CREATE type T_TEST_CA  AS object (mother_code varchar2(50),child_code varchar2(50))
 
-- Definition of table type T_TEST_CA_TABLE
CREATE type  T_TEST_CA_TABLE  AS TABLE of T_TEST_CA
 
--  Definition of my function F_TEST_CA
CREATE OR REPLACE FUNCTION F_TEST_CA RETURN T_TEST_CA_TABLE 
AS
-- Utilisation du table type T_CDC_ENTITY_LINKS_TABLE
v_table T_TEST_CA_TABLE:=T_TEST_CA_TABLE();
v_num integer;
begin
v_num :=0;
 
FOR rec IN (SELECT mother_code,
                   child_code                    
             FROM  TABLE_TEST_CA 
      ) loop
     v_num :=v_num+1;
     v_table.extend(1);
     v_table(v_num) := T_TEST_CA(rec.mother_code, rec.child_code);
end loop;
RETURN v_table;
end F_TEST_CA;
/
 
-- Insert
INSERT INTO TABLE_TEST_CA
   (child_code    ,
    mother_code)
SELECT child_code  ,
       mother_code      
FROM TABLE(F_TEST_CA);

Merci pour votre aide

[Mohamed.Houri]

Votre problème vient du fameux concept qui différencie Oracle des autres bases de données ''Read consistency’’ ou plus exactement ''Statement-Level Read Consistency''. Oracle considère votre function comme ne faisant pas partie de l’instruction insert et donc, il la soumet à son propre ''Read consistent state'' et ne l’associe pas au même SCN (System Commit Number) qu’il attribue à votre INSERT. Et de ce point de vue, votre function semble, ne pas faire partie du même block que l’instruction initiale INSERT. D’où l’erreur.

C’est un peu bizarre mais c’est comme cela que fonctionne Oracle.

Pour résoudre votre problème, utilisez directement le sql qui se trouve dans la fonction plutôt que la fonction elle même. Il serait même mieux du point de vue performance.

Pour plus de détails je vous invite à lire ceci :

http://download.oracle.com/docs/cd/B...htm#sthref1955

"If a SELECT list contains a function, then the database applies statement-level read consistency at the statement level for SQL run within the PL/SQL function code, rather than at the parent SQL level. For example, a function could access a table whose data is changed and committed by another user. For each execution of the SELECT in the function, a new read consistent snapshot is established."

http://harmfultriggers.blogspot.com/

[skuatamad]

Tout à fait d'accord pour n'utiliser qu'une requête mais c'est pas forcément possible de réécrire une fonction table en SQL, en tout cas ça peut être bien compliqué.

Je me disais qu'un contournement pourrait être d'utiliser une CTE avec le hint (non documenté) materialize, ça "fonctionne" mais le hint génère un comportement étrange.
Il est nécessaire de commit pour voir les modifications apportées.
J'avais pourtant des noms de tables temporaires un peu différents par exemple SYS_TEMP_0FD9D6626_1A590B et SYS_TEMP_0FD9D6627_1A590B mais c'est peut être quand même la même table derrière :

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
SQL> INSERT INTO TABLE_TEST_CA (child_code ,mother_code)
  2  WITH t AS (
  3  SELECT /*+ materialize*/ child_code , mother_code
  4    FROM TABLE(cast(F_TEST_CA AS T_TEST_CA_TABLE))
  5  )
  6  SELECT child_code , mother_code FROM t;
 
1 row created.
 
SQL> WITH t AS (
  2  SELECT /*+ materialize*/ child_code , mother_code
  3    FROM TABLE(cast(F_TEST_CA AS T_TEST_CA_TABLE))
  4  )
  5  SELECT child_code , mother_code FROM t;
 
CHILD_CODE                                         MOTHER_CODE
-------------------------------------------------- --------------------------------------------------
0001015                                            0253640
 
SQL> WITH t AS (
  2  SELECT child_code , mother_code
  3    FROM TABLE(cast(F_TEST_CA AS T_TEST_CA_TABLE))
  4  )
  5  SELECT child_code , mother_code FROM t;
 
CHILD_CODE                                         MOTHER_CODE
-------------------------------------------------- --------------------------------------------------
0001015                                            0253640
0001015                                            0253640
 
SQL> WITH t AS (
  2  SELECT /*+ materialize*/ child_code , mother_code
  3    FROM TABLE(cast(F_TEST_CA AS T_TEST_CA_TABLE))
  4  )
  5  SELECT child_code , mother_code FROM t;
 
CHILD_CODE                                         MOTHER_CODE
-------------------------------------------------- --------------------------------------------------
0001015                                            0253640
 
SQL> commit;
 
Commit complete.
 
SQL> WITH t AS (
  2  SELECT /*+ materialize*/ child_code , mother_code
  3    FROM TABLE(cast(F_TEST_CA AS T_TEST_CA_TABLE))
  4  )
  5  SELECT child_code , mother_code FROM t;
 
CHILD_CODE                                         MOTHER_CODE
-------------------------------------------------- --------------------------------------------------
0001015                                            0253640
0001015                                            0253640
 
SQL>

Donc tout dépend comment l'INSERT est utilisé, mais le plus propre et le plus rapide en terme de modification serait plutôt de créer une Global Temporary Table et de l'alimenter avec la fonction PL, puis de loader la table à partir de la table temporaire.

[callenou]

Merci infiniment pour vos réponses, cela nous a permis de trouver une solution de contournement simple pour conserver le même SCN entre le retour de la fonction et l'insert :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE procedure P_TEST_CA IS
v_table T_TEST_CA_TABLE:=T_TEST_CA_TABLE();
begin
 
v_table := F_TEST_CA();
 
INSERT INTO TABLE_TEST_CA
              (child_code    ,
               mother_code)
SELECT child_code  ,
         mother_code      
FROM TABLE(v_table);
end;
 
exec P_TEST_CA;

Fontionne

Par contre, sauriez-vous pourquoi ce code marchait en oracle 10.2 ?
Nous avons posé la question sur métalink : Voici pour l'instant la réponse :
I ran your testcase on different operating systems and versions as you can see in the TESTCASE sections below.

The problem only happened on versions >= 11.1.0.6 when running the testcase in a user schema different from
sys/.... as sysdba, e.g. scott/tiger or a new self-created user.
Granting the DBA role to the user didn't solve the issue.

Can you please double-check this on your system?
Is the testcase also working fine when you connect as sys/change_on_install as sysdba, even on 11G?

[funoracle]

Envoyé par callenou

Par contre, sauriez-vous pourquoi ce code marchait en oracle 10.2 ?
Nous avons posé la question sur métalink : Voici pour l'instant la réponse :
I ran your testcase on different operating systems and versions as you can see in the TESTCASE sections below.

The problem only happened on versions >= 11.1.0.6 when running the testcase in a user schema different from
sys/.... as sysdba, e.g. scott/tiger or a new self-created user.
Granting the DBA role to the user didn't solve the issue.

Can you please double-check this on your system?
Is the testcase also working fine when you connect as sys/change_on_install as sysdba, even on 11G?

Bonjour, je n'ai pas de réponse toute faite mais cela me fait penser qu'il y a peut être un rapport avec le nouveau paramètre 11G DEFERRED_SEGMENT_CREATION (true par défaut) vu que dans le script du début tu as un create table ...
C'est juste une idée comme ça, pas sûr que ce soit en rapport avec ton problème. Il faudrait faire l'essai en mettant DEFERRED_SEGMENT_CREATION à false pour être fixé.

[Mohamed.Houri]

Envoyé par callenou

Par contre, sauriez-vous pourquoi ce code marchait en oracle 10.2 ?
Nous avons posé la question sur métalink : Voici pour l'instant la réponse :
I ran your testcase on different operating systems and versions as you can see in the TESTCASE sections below.

The problem only happened on versions >= 11.1.0.6 when running the testcase in a user schema different from
sys/.... as sysdba, e.g. scott/tiger or a new self-created user.
Granting the DBA role to the user didn't solve the issue.

Can you please double-check this on your system?
Is the testcase also working fine when you connect as sys/change_on_install as sysdba, even on 11G?

Vous n'avez pas besoin de métalink pour cela. Vous pouvez le tester vous-même moyennant la lecture complète de ma première réponse y compris les liens web.

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
 
mhouri.world> SELECT * FROM v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi                
PL/SQL Release 10.2.0.4.0 - Production                                          
CORE	10.2.0.4.0	Production                                                      
TNS FOR Solaris: Version 10.2.0.4.0 - Production                                
NLSRTL Version 10.2.0.4.0 - Production                                          
 
mhouri.world> CREATE OR REPLACE FUNCTION f_new_sal
  2  (p_current_sal IN number) RETURN number AS
  3  --
  4  pl_avg_sal number;
  5  --
  6  begin
  7    --
  8    SELECT avg(SAL) INTO pl_avg_sal
  9      FROM EMP;
 10    --
 11    RETURN p_current_sal + (pl_avg_sal - p_current_sal)/2;
 12    --
 13  end;
 14  /
 
FUNCTION created.
 
mhouri.world> 
mhouri.world> UPDATE EMP e SET e.SAL = f_new_sal(e.SAL)
  2  ;
UPDATE EMP e SET e.SAL = f_new_sal(e.SAL)
                         *
ERROR at line 1:
ORA-04091: TABLE EMP IS mutating, TRIGGER/FUNCTION may NOT see it 
ORA-06512: at "F_NEW_SAL", line 8 
 
 
mhouri.world> rollback;
 
Rollback complete.

[mnitu]

Envoyé par Mohamed.Houri

Vous n'avez pas besoin de métalink pour cela. Vous pouvez le tester vous-même moyennant la lecture complète de mon premier réponse y compris les liens web.
...

J'ai vaguement l'impression que vous lisez ce sujet à travers

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
58
59
60
61
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> CREATE TABLE TABLE_TEST_CA
  2  (
  3    child_code       varchar2(50 byte),
  4    mother_code   varchar2(50 byte)
  5  )
  6  /
 
TABLE created
SQL> INSERT INTO TABLE_TEST_CA VALUES ( '0001015','0253640')
  2  /
 
1 row inserted
SQL> -- Definition of type T_TEST_CA
SQL> CREATE type T_TEST_CA  AS object (mother_code varchar2(50),child_code varchar2(50))
  2  /
 
Type created
SQL> -- Definition of table type T_TEST_CA_TABLE
SQL> CREATE type  T_TEST_CA_TABLE  AS TABLE of T_TEST_CA
  2  /
 
Type created
SQL> --  Definition of my function F_TEST_CA
SQL> CREATE OR REPLACE FUNCTION F_TEST_CA RETURN T_TEST_CA_TABLE
  2  AS
  3  -- Utilisation du table type T_CDC_ENTITY_LINKS_TABLE
  4  v_table T_TEST_CA_TABLE:=T_TEST_CA_TABLE();
  5  v_num integer;
  6  begin
  7  v_num :=0;
  8  
  9  FOR rec IN (SELECT mother_code,
 10                     child_code
 11               FROM  TABLE_TEST_CA
 12        ) loop
 13       v_num :=v_num+1;
 14       v_table.extend(1);
 15       v_table(v_num) := T_TEST_CA(rec.mother_code, rec.child_code);
 16  end loop;
 17  RETURN v_table;
 18  end F_TEST_CA;
 19  /
 
FUNCTION created
SQL> -- Insert
SQL> INSERT INTO TABLE_TEST_CA
  2     (child_code    ,
  3      mother_code)
  4  SELECT child_code  ,
  5         mother_code
  6  FROM TABLE(F_TEST_CA)
  7  /
 
1 row inserted
 
SQL>

[mnitu]

Et pour la 11 plantage

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
58
59
60
61
62
63
64
65
66
67
68
69
 
Connected TO Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 
Connected AS mni
 
SQL> 
SQL> CREATE TABLE TABLE_TEST_CA
  2  (
  3    child_code       varchar2(50 byte),
  4    mother_code   varchar2(50 byte)
  5  )
  6  /
 
TABLE created
SQL> INSERT INTO TABLE_TEST_CA VALUES ( '0001015','0253640')
  2  /
 
1 row inserted
SQL> -- Definition of type T_TEST_CA
SQL> CREATE type T_TEST_CA  AS object (mother_code varchar2(50),child_code varchar2(50))
  2  /
 
Type created
SQL> -- Definition of table type T_TEST_CA_TABLE
SQL> CREATE type  T_TEST_CA_TABLE  AS TABLE of T_TEST_CA
  2  /
 
Type created
SQL> --  Definition of my function F_TEST_CA
SQL> CREATE OR REPLACE FUNCTION F_TEST_CA RETURN T_TEST_CA_TABLE
  2  AS
  3  -- Utilisation du table type T_CDC_ENTITY_LINKS_TABLE
  4  v_table T_TEST_CA_TABLE:=T_TEST_CA_TABLE();
  5  v_num integer;
  6  begin
  7  v_num :=0;
  8  
  9  FOR rec IN (SELECT mother_code,
 10                     child_code
 11               FROM  TABLE_TEST_CA
 12        ) loop
 13       v_num :=v_num+1;
 14       v_table.extend(1);
 15       v_table(v_num) := T_TEST_CA(rec.mother_code, rec.child_code);
 16  end loop;
 17  RETURN v_table;
 18  end F_TEST_CA;
 19  /
 
FUNCTION created
SQL> -- Insert
SQL> INSERT INTO TABLE_TEST_CA
  2     (child_code    ,
  3      mother_code)
  4  SELECT child_code  ,
  5         mother_code
  6  FROM TABLE(F_TEST_CA)
  7  /
 
INSERT INTO TABLE_TEST_CA
   (child_code    ,
    mother_code)
SELECT child_code  ,
       mother_code
FROM TABLE(F_TEST_CA)
 
ORA-04091: la TABLE MNI.TABLE_TEST_CA est en mutation ; le déclencheur ou la fonction ne peut la voir
ORA-06512: à "MNI.F_TEST_CA", ligne 9
 
SQL>

[mnitu]

A noter que l'exemple suivant fonctionne en 11g

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Connected TO Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 
Connected AS mni
 
SQL> 
SQL> CREATE TABLE TABLE_TEST_CA1 AS
  2  SELECT cast(rownum AS int) id,
  3         t.*
  4    FROM table_test_ca t
  5  /
 
TABLE created
SQL> INSERT INTO TABLE_TEST_CA1
  2  SELECT rownum,
  3         child_code  ,
  4         mother_code
  5  FROM TABLE(F_TEST_CA)
  6  /
 
1 row inserted
 
SQL>

[Mohamed.Houri]

Envoyé par mnitu

J'ai vaguement l'impression que vous lisez ce sujet à travers

Je voulais tout simplement préciser que l'erreur

Code :

1
2
 
ORA-04091: TABLE EMP IS mutating, TRIGGER/FUNCTION may NOT see it

existait déjà en 10g. Et que si leur code fonctionnait avant et qu'il ne fontionne plus maintenant et bien tant mieux car Oracle vient de corriger manifestement un bug qui existait en 10g et qui leur permettait de faire des updates sur une table mutante.

Ceci dit, merci d'avoir montré que le même code qui fonctionnait en 10g ne fonctionne plus en 11g. De plus, si quelqu'un peut reproduire le cas en 8i et en 9i, nous pourrions alors savoir si le même problème se produisait dans ces deux vielles versions

[pacmann]

Salut !

Envoyé par Mohamed.Houri

...SCN (System Commit Number)...

Juste pour faire chier un peu : SCN = System Change Number.

Quand on parle du moment où on en attribue un au commit, on dit Commit SCN (genre à contrario de celui qui est attribué au début du statement pour définir le snapshot courant).

[Mohamed.Houri]

Envoyé par pacmann

Salut !



Juste pour faire chier un peu : SCN = System Change Number.

Quand on parle du moment où on en attribue un au commit, on dit Commit SCN (genre à contrario de celui qui est attribué au début du statement pour définir le snapshot courant).

Vous avez tout à fait raison de faire cette remarque très juste. Merci

Lorsqu’une requête ou une transaction est commencée, elle fait appel à une procédure interne nommée (kcmgss - Get Snapshot SCN) et à chaque fois qu’une transaction fait un commit elle augment son SCN via une autre routine appelée (kcmgas- Get and Advance SCN). Par contre, il existe d’autres situations, autre que le commit, où le SCN est incrémenté. C’est pourquoi le nom de System Change Number est plus approprié que System Commit Number.

Je suis en train de lire un livre de Jonathan Lewis où tout ce genre de petits et intéressants détails est mentionné avec beaucoup de précision

[mnitu]

Envoyé par Mohamed.Houri

...Et que si leur code fonctionnait avant et qu'il ne fontionne plus maintenant et bien tant mieux car Oracle vient de corriger manifestement un bug qui existait en 10g et qui leur permettait de faire des updates sur une table mutante.

...

Je suis désolé d’insister mais vous lisez à travers et vous ne comprenez pas bien la différence entre la situation qui génère correctement l’erreur de la table mutante (votre exemple) et celle ci.
En fait il n’y a aucun raison d’avoir une erreur de la table en mutation dans ce cas. La table n’est pas en mutation au moment de la lecture par la fonction et la fonction n’est pas pipelined ! Par conséquence la fonction va lire tous les enregistrements en mémoire avant de donner le contrôle à l’environnement appelant. Dans ce cas il n’y aucun risque d’avoir des résultats qui dépendent de l’ordre d’exécution des divers étapes et par conséquence du besoin de se protéger via l’erreur de la table en mutation.
Oui il y a un bug mais il est dans oracle 11g et non pas avant. Analysez aussi les exemples fournis pour la version 11g qui fonctionnent. Demandez-vous aussi pourquoi le support Oracle ne réponde pas comme vous.

[pachot]

Bonjour Mohamed,

Envoyé par Mohamed.Houri

Je suis en train de lire un livre de Jonathan Lewis où tout ce genre de petits et intéressants détails est mentionné avec beaucoup de précision

Et, est-ce que ça ne te donne pas une envie folle d'en faire une traduction française ?

Cordialement,
Franck.

[Mohamed.Houri]

Envoyé par pachot

Bonjour Mohamed,


Et, est-ce que ça ne te donne pas une envie folle d'en faire une traduction française ?

Cordialement,
Franck.

Salut Franck,

J'ai vraiment pensé à ça. Mais uniquement si Apress me donne l'autorisation de faire circuler cette traduction. Autrement, je saisi cette occasion pour te remercier de m'avoir fait venir sur ce forum où j'ai appris beaucoup de choses et où j'ai pu enfin utiliser la langue française dans le monde Oracle.