insert dans Forall et transactions

Bonjour,

je reprends un package PL/SQL et je m'interroge sur le comportement de plusieurs sections.

Je vous en donne un exemple simplifié.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

select F11, F21, F31 bulk collect into var1, var2, var3 from MaTable1   Begin   Forall i in var1.First...var1.Last   insert into MaTable2 (F12, F22, F32) values (var1(i), var2(i), var3(i));   Exception when others then Rollback; End; Commit;

---

Donc ma question est la suivante: combien de transactions sont ouvertes par l'exécution de ce segment de code? Je ne parviens pas à déterminer quel est le comportement d'Oracle lorsque des ordres "LMD" sont appelés dans une boucle.

Chaque ordre Insert ouvre implicitement une nouvelle transaction mais quand est elle fermée (Insert suivant, ordre Commit final)?

Ou alors n'y a t'il qu'une transaction globale ouverte par le begin puis terminée par le rollback ou le commit?



L'idée est de savoir si une telle requête peut faire grossir le tablespace UNDO de manière importante ou non.

Merci d'avance!

Note: la base est sous Oracle 10g

La notion de transactions imbriquées n'existe pas par définition. Il n'y a jamais qu'une seule transaction.

A +

Merci pour cette réponse mais je ne suis toujours pas sûr de ce que cela implique en pratique.

Dans l'exemple que je présente, si une exception est levée au troisième tour de boucle et qu'un rollback est déclenché la table sera-t-elle:
1/ identique à ce qu'elle était avant le forall?
2/ modifiée par les deux insert précédents?

D'un point de vue "gestion mémoire", y a t'il une "sauvegarde" de la table au moment du Begin puis une nouvelle avant chacun des insert ou alors uniquement au moment du begin? Si plusieurs images de la table sont faites, les précédentes sont elles supprimées?

Désolé d'insister mais j'aimerai vraiment comprendre le comportement du moteur dans ce cas....:bug:

Merci d'avance

Ni le fait d'avoir un forall, ni le fait de faire des insert ne vont lancer un commit.... Donc je pense qu'un rollback lancé par la suite va annuler tous les insert qui ont été faits.

C'est pas très dur à vérifier, après ton BULK COLLECT tu peux faire

Code:

---

var1(2) := une_valeur_incohérente

---

et vérifier ensuite que l'insertion de ta première ligne a bien été annulée.

Accessoirement avec le forall, tu fais tous les insert en une seule opération (c'est son intérêt par rapport à une simple boucle for) au lieu de faire des aller-retours SQL <--> PL/SQL. Mais je ne pense pas que ça change quoique ce soit au niveau du rollback.

Bonjour,

D'après moi tu n'as qu'une transaction, commitée à la fin du traitement si tu sors correctement du forall. Les inserts sont les ordres qui consomment le moins d'UNDO mais je ne connais ni la volumétrie de ta table, ni celle de ton tbs UNDO ;)

Merci pour vos réponses, on est donc bien dans un comportement de type "tout ou rien" avec une transaction unique.

Par contre pour ce qui est de l'augmentation de la taille de l'UNDO, est ce que ce type de requête avec des ordres DELETE ou UPDATE peut induire une augmentation notable de ce tablespace?

D'ailleurs certaines boucles n'utilisent pas la commande FORALL (qui isole le PL/SQL du moteur SQL comme l'a rappelé Brassouille) mais de "simples" FOR?

Dans le cas qui m'intéresse, le tablespace UNDO occupe autant d'espace que les data... :aie:

Sinon, y a t'il une solution pour réduire la taille (purger?) ce tablespace en cours de traitement autrement que par des commit/rollback?

Oui, tu peux consommer massivement des undo, surtout avec des delete.
Et non il n'y a pas d'autre moyens de "libérer" de l'espace undo autrement qu'avec un commit ou un rollback (et heureusement, l'undo ne servirait à rien sinon ;) )
une transaction rollbackée implique la libération de l'espace undo associé, une transaction commitée non (sauf au bout de undo_retention secondes ou si besoin par manque d'espace)

Citation:

---

Envoyé par Mathias44

une transaction rollbackée implique la libération de l'espace undo associé, une transaction commitée non

---

?? Je pensais que quand tu commitais, tu vidais aussi le UNDO associé?? à quoi ça peut servir de le conserver...?

Pour une flashback query : si tu souhaites pouvoir exécuter une requête dans un état transactionnel antérieur. Sachant qu'en 9i tu n'as pas de moyen de garantir cette rétention à part surdimensionner ton tablespace.

Citation:

---

Envoyé par SQLpro

Il n'y a jamais qu'une seule transaction.

---

En fait, une session Oracle peut avoir 2 transactions indépendantes démarrées grâce aux transactions autonomes.

Citation:

---

Envoyé par Mathias44

Pour une flashback query : si tu souhaites pouvoir exécuter une requête dans un état transactionnel antérieur. Sachant qu'en 9i tu n'as pas de moyen de garantir cette rétention à part surdimensionner ton tablespace.

---

Le undo sert aussi à fournir les images avant des blocs modifiés par la transaction qui se termine par COMMIT aux requêtes qui ont démarré avant cette transaction: c'est le principe du read consistency.

Merci beaucoup pour ces informations notamment sur le fonctionnement du COMMIT.

Citation:

---

Envoyé par pifor

Le undo sert aussi à fournir les images avant des blocs modifiés par la transaction qui se termine par COMMIT aux requêtes qui ont démarré avant cette transaction: c'est le principe du read consistency.

---

Nous parlions des blocs undo des transactions commitées
(et si la transaction est commitée, il n'y a plus lieu d'utiliser l'image avant ;) )

Citation:

---

Envoyé par Mathias44

Nous parlions des blocs undo des transactions commitées
(et si la transaction est commitée, il n'y a plus lieu d'utiliser l'image avant ;) )

---

T'es certain ?

Session 1

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> create table t_commit 2 as 3 select * 4 from all_objects 5 where rownum <= 10;   Table créée.   SQL> variable rc refcursor; SQL> exec open :rc for select * from t_commit;   Procédure PL/SQL terminée avec succès.

---

Session 2

Code:

---

1 2 3 4 5 6 7 8 9 10

SQL> delete t_commit;   10 ligne(s) supprimée(s).   SQL> commit;   Validation effectuée.   SQL>

---

Session 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

SQL> print rc   OWNER OBJECT_NAME ------------------------------ ------------------------------ SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ----------------- CREATED LAST_DDL TIMESTAMP STATUS T G S NAMESPACE -------- -------- ------------------- ------- - - - ---------- EDITION_NAME ------------------------------ SYS ICOL$ 20 2 TABLE 15/10/07 15/10/07 2007-10-15:10:09:08 VALID N N N 1       OWNER OBJECT_NAME ------------------------------ ------------------------------ SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ----------------- CREATED LAST_DDL TIMESTAMP STATUS T G S NAMESPACE -------- -------- ------------------- ------- - - - ---------- EDITION_NAME ------------------------------ ...

---

Où pense tu qu'il a trouvé les données supprimées et "commitée" par la Session 2 ?

Bonsoir,

Effectivement ma remarque ne s'applique qu'aux transactions qui démarrent après, je n'ai pas tenu compte du cas que tu exposes - et en le relisant que pifor également du reste ;)