Le rollback explose au moment du FETCH d'un Curseur

Bonjour,

Lors de l'execution d'une Proc_stock, j'obtient une erreur Oracle ORA-01555 (RBS trop petit) au moment où mon curseur effectue un fetch :

OPEN curStatut;
loop
FETCH curStatut into v_Statut;
EXIT WHEN curStatut%NOTFOUND;


C'est bizarre car ce curseur est appelé plusieurs fois via une boucle (à chaque fois j'ouvre le curseur, je le fetche, j'execute mon programme et je le ferme). Or il ne plante qu'au bout d'un certain nombre d'utilisation. Pourtant le nombre de lignes ramenées à chaque ouverture est sensilement le même.

Mes questions :
- Le curseur n'est-il pas complètement vidée de la mémoire au moment du 'close' ? Oracle ne sait-il pas gérer correctement la mémoire ?
- Je ne peux pas minimiser le nombre de lignes ramenées par mon curseur. Existe-t-il une autre méthode pour en limiter la taille ?

Toute vos infos sont appréciées.
Merci

P.S : Oracle 8.0.6

Le rôle d'un segment de rollback est de conserver l'état des données initiales.
Pensez-vous à commiter régulièrement vos modifications ?

Bonjour,

L'erreur Oracle 1555 à laquelle tu es confrontée peut provenir de 2 choses :
1 ) un RBS effectivement trop petit,
2 ) un pb de lecture cohérente des données.

Pour que je puisse t'aider, il faudrait m'en dire plus, à savoir :
- décrire ce que fait ton curseur, ou à défaut me donner le code (est-ce que par hasard, ton curseur te remonte tes données, que tu les mets à jour, et que de temps en temps tu commites),
- lorsque tu lances ta procédure stockée, y a-til des transactions concurrentes aux tiennes ? Y a-t il des requêtes ou des batch qui travailleraient sur les mêmes données que les tiennes et en même temps, ou es-tu seul à travailler ???

Oui, oui, je commite à tout moment.

C'est justement ce qui m'inquiète! Pourquoi le roolback explose-t-il au moment du remplissage du curseur ?

J'ai peut être un sourci au miveau de mes rollback. J'en ai plusieurs de petite taille. Or il semble que lors d'un fetch Oracle ne sache pas passer à un autre rollback segment si le premier est plein.

Le problème (aussi) est que je n'ai pas de droits DBA sur cette base. Est-il possiblede voir le nombre et la taille des RBS sans privilèges DBA ?

Merci de m'aider
Voici un peu mon code.
Tout cela est executé à l'intérieur d'un package

Dans une première procédure, le lance en boucle la procédure p_imag_enchainements_statut avec le nom d'un programme en paramètre :

Citation:

---

-- liste de tous mes programmes immobiliers
open curProgE;
loop
fetch curProgE into v_ProgE;
EXIT WHEN curProgE%NOTFOUND;

p_imag_enchainements_statut(v_ProgE.code_programme);

end loop;
close curProgE;

---

Voici le code de p_imag_enchainements_statut :

Citation:

---

procedure p_imag_enchainements_statut (li_CodeProgramme in varchar2) IS

CURSOR curStatut IS
select t.id_detail_statut from tf_detail_statut t, tr_programmes p
where t.indic_alim = 'à mettre à jour'
and t.id_programme = p.id_programme
and p.code_programme = li_CodeProgramme;
v_Statut curStatut%ROWTYPE;

OPEN curStatut;
loop
fetch curStatut into v_Statut; <--- Ca plante ici
EXIT WHEN curStatut%NOTFOUND;

ici quelques ordres inseert update dans des if,then,else
avec des commit

end loop; -- fin boucle du cursor CurStatut
commit;
close curStatut;

END p_imag_enchainements_statut;

---

A priori, je suis le seul sur cette base pour le moment, il n'y a donc pas de surcharge ...

Pour ta 1ère procédure, peux-tu me donner la définition de ton curseur curProgE ???

Dans ta 2ième procédure, tu m'indiques que tu fais des insert et des updates. Peux-tu simplement me donner le nom des tables dans lesquelles il y a des INSERT, et le nom des tables dans lesquelles il y a des UPDATE.

Au premier abord, je pense que ce n'est pas un pb de taille de tes RBS. C'est comme je te le disais un pb de lecture cohérente de données. En fait, tu dois committer tellement souvent que les entrées dans ton RBS ne servent plus et sont écrasées, alors que ton curseur curProgE a besoin lui de ces entrées.

J'attends ta réponse et je te donne plus d'explications en retour.

Problème de lecture cohérente ?

Citation:

---

Au premier abord, je pense que ce n'est pas un pb de taille de tes RBS. C'est comme je te le disais un pb de lecture cohérente de données. En fait, tu dois committer tellement souvent que les entrées dans ton RBS ne servent plus et sont écrasées, alors que ton curseur curProgE a besoin lui de ces entrées.

---

Je vois ce que tu veux dire mais ma table tr_programmes n'est pas utilisée dans mes ordres SQL.

Voici mon curseur curProgE :

Citation:

---

CURSOR curProgE IS
select distinct p.code_programme from tr_programmes p;
v_ProgE curProgE%ROWTYPE;

---

Voici quelques ordres SQL que j'apelle apres le FETCH :

Citation:

---

select id_ste_gerante, id_programme, id_lot, id_typo, id_adb, id_agpromo, id_agloc, id_partenaire, id_assistant, id_statut, no_ordre, etat, numlocation, date_deb_statut, date_fin_statut_r, date_fin_statut_p, duree_statut_r, duree_statut_p, indic_alim, date_alim
into tfIdSteGerante, tfIdProgramme, tfIdLot, tfIdTypo, tfIdAdb, tfIdAgpromo, tfIdAgloc, tfIdPartenaire, tfIdAssistant, tfIdStatut, tfNoOrdre, tfEtat, tfNumLocation, tfDateDebStatut, tfDateFinStatutR, tfDateFinStatutP, tfDureeStatutR, tfDureeStatutP, tfIndicAlim, tfDateAlim
from tf_detail_statut
where id_detail_statut = v_Statut.id_detail_statut;


-- Mise à jour du statut
update tf_detail_statut
set etat = li_etat,
date_fin_statut_r = tfDateFinStatutR,
evt_fin_r = li_EvtFinR,
date_fin_statut_p = tfDateFinStatutP,
evt_fin_p = li_EvtFinP,
duree_statut_r = li_tfDateFinStatutR- tfDateDebStatut,
duree_statut_p = li_tfDateFinStatutP- tfDateDebStatut,
indic_alim = 'mis à jour',
date_alim = sysdate
where id_detail_statut = tfIdDetailStatut;
commit;

---

S'il s'avère que c'est un problème de taille du curseur, je vais essayer de créer une table temporaire pour stocker les valeurs de mon curseur et parcourir chaque enregistrements de la table. je ne vois pas comment faire d'autre ...

En tout cas, merci pour votre aide

Ok, c'est bien un pb de lecture cohérente des données.

Je m'explique : dans ta 2ième procédure, tu ouvres ton curseur curStatus et tu fetches dessus. Ce curseur se sert entre autre de ta table TF_DETAIL_STATUT.

Et puis à un moment, tu mets à jour ton status avec ton UPDATE TF_DETAIL_STATUT.

A ce moment-là, tu es bien d'accord avec moi que les anciennes valeurs de ta table TF_DETAIL_STATUT sont stockées dans le Rollback Segment.

Or Oracle te garantit une lecture cohérente des données, c'est-à-dire que sur le prochain fetch (celui de curStatus), Oracle ne va pas chercher les données que tu viens de mettre à jour (et qui sont dans le cache des données, en status Dirty), mais il va chercher les données qui sont dans le Rollback Segment.

Le pb, il vient tout simplement du fait que tu commites après l'update. En fait, en committant, tu dis à Oracle de valider ta mise à jour. A ce moment-là, Oracle n'a plus besoin de conserver les valeurs dans le Rollback Segment. Le prochain UPDATE d'ailleurs les écrases.

Mais ton curseur curStatus, quand on fetche dessus à nouveau, Oracle essaye de te donner les valeurs qui sont dans le RBS, mais elles sont écrasées.

C'est ce qui signifie l'erreur que tu as eu. Comme beaucoup de gens, tu n'as retenu qu'une partie du message (RBS too small). Or le début du message, c'est SNAPSHOT TOO OLD, c'est-à-dire cliché trop vieux.

Pour les développeurs Oracle, c'est un des pb rencontrés le plus dur à comprendre et à corriger.

Moi ce que je te conseille, c'est de virer tous tes COMMIT intermédiaires. Tu ne dois garder que :

Citation:

---

end loop; -- fin boucle du cursor CurStatut
commit;
close curStatut;

---

C'est clair pour toi la lecture cohérente, ou pas ???

Si ce n'est pas clair, j'essairais de te l'expliquer autrement.

-- Mise à jour du statut
update tf_detail_statut
set etat = li_etat,
date_fin_statut_r = tfDateFinStatutR,
evt_fin_r = li_EvtFinR,
date_fin_statut_p = tfDateFinStatutP,
evt_fin_p = li_EvtFinP,
duree_statut_r = li_tfDateFinStatutR- tfDateDebStatut,
duree_statut_p = li_tfDateFinStatutP- tfDateDebStatut,
indic_alim = 'mis à jour',
date_alim = sysdate
where id_detail_statut = tfIdDetailStatut;
commit;


/***********


CURSOR curStatut IS
select t.id_detail_statut from tf_detail_statut t, tr_programmes p
where t.indic_alim = 'à mettre à jour'
and t.id_programme = p.id_programme
and p.code_programme = li_CodeProgramme;
v_Statut curStatut%ROWTYPE;

OPEN curStatut;
loop
fetch curStatut into v_Statut; <--- Ca plante ici
EXIT WHEN curStatut%NOTFOUND;

A priori, la table tf_detail_statut mise à jour dans le code fait également parti du curseur principal.

Pourquoi ne pas utiliser le select for update dans le curseur, puis
update where current of dans le code ?

J'AI COMPRIS !

Effectivement, je comprend mieux comme ça.
Je teste et je vous tient au courant.
En tout cas, merci pour cette explication

C'est bon, ça marche. Merci à tous.