Erreur ORA-4030 dans Procédure PL/SQL

Bonjour,

je génère du code XML via une procédure stockée sur un Oracle 10 g R1 sous linux Red hat.
Lors d'un fecth d'un curseur j'ai un problème mémoire sur un sort des données générant une erreur de type ORA-04030: mémoire de traitement manquante lors d'affectation de 4008 octets (kxs heap,kokeglb: kokegPinLob).

Ci-dessous mon curseur:
Cursor C_FICPT_AG is
(SELECT qry3b.codetare, XMLAgg((resultat3b) order by substr(resultat3b,1,50)).getClobVal() as resultat3 FROM (SELECT rz_f_ficpt_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Compteur",
XMLElement(name "NumCarCo", rz_f_ficpt_cara_genprel.numcarco),
XMLElement(name "Car",rz_f_ficpt_cara_genprel.car),
XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Compteur",
XMLElement(name "NumPriEa", rz_f_ficpt_cara_genprel.numpriea),
XMLElement(name "ValCarCo",rz_f_ficpt_cara_genprel.valcarco)
) order by rz_f_ficpt_cara_genprel.numpriea)
) ) as resultat3b
FROM rz_f_ficpt_cara_genprel, rz_f_genprel
WHERE rz_f_ficpt_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and substr(rz_f_genprel.codetare,1,2)=w_numdep
GROUP BY rz_f_ficpt_cara_genprel.codetare,numcarco,car) qry3b
GROUP BY qry3b.codetare);

sans l'order by en rouge cela fonctionne bien.

La table rz_f_ficpt_cara_genprel contient 97896 rows
La table rz_f_genprel contient 12113 rows

je fetch le résultat en bulk collect dans un tableau interne de type clob.
puis j'insere dans une table temporaire de clob.

ma sort_area est positionnée sur 2 Mo

merci de votre aide

Il manque de la mémoire au niveau de la machine pour la PGA.
Essayez de diminuer la consommation d'autres services sur la machine et d'augmenter SORT_AREA_SIZE ou d'utiliser PGA_AGGREGATE_TARGET.

merci de votre réponse,

La PGA_TARGET a été augmenté à 56 Mo et ça ne suffit pas, tjs la même erreur. le WORKAREA_SIZE_POLICY est bien en AUTO donc on l'utilise.
On l'augmente à 100 Mo

est-ce que ces paramètres sont dynamiques ??
le fait d'augmenter excessivement la PGA_AGGREGATE_TARGET peut il générer d'autre conflit sur la base ?

Le paramètre PGA_AGGREGATE_TARGET est bien dynamique.

Mais en fait ORA-04030 signifie d'abord qu'il n'y a plus assez de mémoire au niveau Linux. Il faudrait d'abord comprendre pourquoi.

Y-a-t-il une seule instance oracle sur la machine ?
Ou d'autres services (serveur web/serveur d'applications) ?
Quelle est la taille de la mémoire de la machine ?

Que retournent pour chaque instance sur la machine:

Code:

---

1 2 3

show sga; select * from v$pgastat where name like '%alloc%';

---

la machine linux est un red hat 4 ES,
la mémoire totale est de 8 Go
il n'y pas de sevice web/client sur la machine
il y a 3 instances actives.
il y a un moteur carto qui prend 1.2 go

voici les sga de chaque instances:
1ere 2.6go
2ème 1.6 go
3ème 800mo

La mémoire libre a été augmentée.
on a suivi le traitement qui prend environ 700 Mo de mémoire physique.

voici l'erreur maintenant dans la trace PL/SQL

ERREUR Ã* la ligne 1 :
ORA-04030: mémoire de traitement manquante lors d'affectation de 22172 octets (PLS non-lib hp,PH1DRV ENGINE SPACE)
ORA-06512: Ã* "FLUXTDECL.RZ_PROC_P_FLUX_GENPREL", ligne 164
->curseur C_ACT_AG

ORA-06512: Ã* "FLUXTDECL.RZ_PROC_P_FLUX_GENPREL", ligne 1144
-> fetch curseur C_ACT_AG

Sans l'order by celà fonctionne...

je vous mets le sql lanceur et la procédure car je pense qu'il y a un problème dans le code.
j'ai enlevé deux autre curseurs qui font le même traitement et la phase de pré insertion des données dans les tables RZ_F....

la structure de la table RES_PREL_TEMP est (varchar2, clob, clob, clob, clob, clob)

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

SET SERVEROUTPUT ON SIZE 60000 SET APPINFO OFF SET HEAD OFF SET FEED OFF SET PAGESIZE 0 SET LONG 560 SET LIN 560   WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT OSERROR   -- Connexion Ã* la base CONNECT &1/&2   --TRUNCATE TABLE RZ_F_GENPREL;   --TRUNCATE TABLE RZ_F_CPT_GENPREL;   --TRUNCATE TABLE RZ_F_FICPT_CARA_GENPREL;   --TRUNCATE TABLE RZ_F_ACT_CARA_GENPREL;   TRUNCATE TABLE RES_PREL_TEMP;   alter session set optimizer_mode = ALL_ROWS;   /*test infructueux*/ --alter session set workarea_size_policy = manual; --alter session set hash_area_size = 20000000; --alter session set sort_area_size = 20000000;   EXECUTE RZ_PROC_P_FLUX_GENPREL ('&3')   -- Sortie de Sqlplus EXIT

---

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 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698

CREATE OR REPLACE PROCEDURE Rz_Proc_P_FLUX_GENPREL (matricule IN varchar2) IS BEGIN DECLARE TYPE TYP_TAB_RES1 IS TABLE OF RES_PREL_TEMP.CODETARE%TYPE ; TYPE TYP_TAB_RES2 IS TABLE OF RES_PREL_TEMP.RESULT4%TYPE ; tabrec_AG1 TYP_TAB_RES1; tabrec_AG2 TYP_TAB_RES2; -- declaration des variables DirFic VARCHAR2(50); DirLog VARCHAR2(50); NomFichier VARCHAR2(50); NomLog VARCHAR2(50); vo_errm VARCHAR2(256); vo_FileDesc UTL_FILE.FILE_TYPE; vo_FileDescLog UTL_FILE.FILE_TYPE; -- declaration des variables du fichier v_lob_loc CLOB; v_concat_lob_loc CLOB; v_buffer VARCHAR2(32767); v_amount BINARY_INTEGER; v_offset NUMBER(38) := 1; v_out_file UTL_FILE.FILE_TYPE; length INTEGER; -- declaration des variables du xml ligne1 VARCHAR2(200); ligne2 VARCHAR2(200); ligne3 VARCHAR2(200); ligne4 VARCHAR2(200); ligne5 VARCHAR2(200); ligne6 VARCHAR2(200); ligne7 VARCHAR2(200); ligne8 VARCHAR2(200); ligne9 VARCHAR2(200); ligne10 VARCHAR2(200); wnumseq_suivi NUMBER(9); wnumseq_err NUMBER(9); w_eta VARCHAR2(2); w_codtypred VARCHAR2(3); w_codage VARCHAR2(2); w_numtypfo VARCHAR2(5); w_numfor NUMBER(3); w_libfor VARCHAR2(50); w_anndec NUMBER(4); w_anndec1 NUMBER(4); w_anndec2 NUMBER(4); w_anndec3 NUMBER(4); w_nenv1 NUMBER(3); w_nenv2 NUMBER(3); w_nenv3 NUMBER(3); w_dlimde VARCHAR2(10); w_sysdate Date; w_numdep VARCHAR2(2); w_matricule VARCHAR2(9); --Variable en dur pour le flux w_nomins VARCHAR2(60); w_refins VARCHAR2(60); w_telins VARCHAR2(20); w_mailins VARCHAR2(60); w_usermv VARCHAR2(10); -- variables indices v_nbrows INTEGER; j INTEGER; -- declaration des curseurs Cursor C_DEP_AG is --Select distinct substr(codetare,1,2) numdep from rz_f_genprel Where rz_f_genprel.cappli='AG'; Select distinct codetare mat from rz_f_genprel Where rz_f_genprel.cappli='AG'; Cursor C_FICPT_AG is (SELECT qry3b.codetare, XMLAgg((resultat3b) order by substr(resultat3b,1,40)).getClobVal() as resultat3 FROM (SELECT rz_f_ficpt_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Compteur", XMLElement(name "NumCarCo", rz_f_ficpt_cara_genprel.numcarco), XMLElement(name "Car",rz_f_ficpt_cara_genprel.car), XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Compteur", XMLElement(name "NumPriEa", rz_f_ficpt_cara_genprel.numpriea), XMLElement(name "ValCarCo",rz_f_ficpt_cara_genprel.valcarco) ) order by rz_f_ficpt_cara_genprel.numpriea) ) ) as resultat3b FROM rz_f_ficpt_cara_genprel, rz_f_genprel WHERE rz_f_ficpt_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and /*substr(rz_f_genprel.codetare,1,2)=w_numdep*/ rz_f_genprel.codetare=w_matricule GROUP BY rz_f_ficpt_cara_genprel.codetare,numcarco,car) qry3b GROUP BY qry3b.codetare); Cursor C_ACT_AG is (SELECT qry4b.codetare, XMLAgg((resultat4b) order by substr(resultat4b,1,40)).getClobVal() as resultat4 FROM (SELECT rz_f_act_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Activite", XMLElement(name "NumCarAc", rz_f_act_cara_genprel.numcarac), XMLElement(name "Car",rz_f_act_cara_genprel.car), XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Activite", XMLElement(name "NumPriEa", rz_f_act_cara_genprel.numpriea), XMLElement(name "ValCarAc",rz_f_act_cara_genprel.valcarac) ) order by rz_f_act_cara_genprel.numpriea) ) ) as Resultat4b FROM rz_f_act_cara_genprel,rz_f_genprel WHERE rz_f_act_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and /*substr(rz_f_genprel.codetare,1,2)=w_numdep*/ rz_f_genprel.codetare=w_matricule GROUP BY rz_f_act_cara_genprel.codetare,numcarac,car) qry4b GROUP BY qry4b.codetare); CURSOR cur_flux_genprel IS SELECT '<FormulaireGeneralPrelevement>'||result1||result2||result3||result4||result5||'</FormulaireGeneralPrelevement>' as resultat0 FROM RES_PREL_TEMP WHERE RES_PREL_TEMP.codetare = NVL(matricule, RES_PREL_TEMP.codetare); BEGIN DirFic := '/home/exploit/FLUXTELEDECLA/XML/'; DirLog := '/home/exploit/FLUXTELEDECLA/'; NomFichier := 'F18_FGenPre.xml'; NomLog := 'RZ_PROC_P_FLUX_GENPREL.LOG'; -- initialisation des variables du xml ligne1 := '<?xml version="1.0" encoding="UTF-8"?>'; ligne2 := '<!--Sample XML file generated by XMLSpy v2008 sp1 (http://www.altova.com)-->'; ligne3 := '<FORMULAIRES_FGenPre xsi:noNamespaceSchemaLocation="F18_FGenPre.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'; ligne4 := '<Agence><CodAge>'; ligne5 := '</CodAge></Agence>'; ligne6 := '<Formulaire><AnnRed>'; ligne7 := '</AnnRed>'; ligne8 := '<DatLimDe>'; ligne9 := '</DatLimDe>'; ligne10 := '</Formulaire></FORMULAIRES_FGenPre>'; wnumseq_suivi := 0; wnumseq_err := 0; w_eta := 'OK'; w_numfor := 18; w_mailins := 'service.dr@eau-loire-bretagne.fr'; -- Ouverture du fichier LOG -- BEGIN vo_FileDescLog := UTL_FILE.FOPEN ( DirLog,NomLog,'A'); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirLog||NomLog||'.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirLog||NomLog||' invalide.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirLog||NomLog||' ne peut pas etre ouvert.'); WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du fichier ' ||DirLog||NomLog||' : ' || vo_errm); END; -- Recuperation de la date et heure du jour SELECT sysdate INTO w_sysdate FROM dual; -- Récupération du code Agence BEGIN SELECT CodAge INTO w_codage FROM RZ_R_AGE; EXCEPTION WHEN NO_DATA_FOUND THEN w_codage:=''; WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr, RefFlux, DatErr, RefClef, CodErr, DesErr) VALUES (wnumseq_err, NomFichier, w_sysdate, null, 'Lecture RZ_R_AGE', vo_errm); COMMIT; END; ---------------------------------------------------------------------------------------------------------------------- -- -- Création du fichier Xml -- BEGIN NomFichier := w_codage||'_'||NomFichier; v_out_file := UTL_FILE.FOPEN(DirFic,NomFichier,'W',32767); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirFic||NomFichier||'.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirFic||NomFichier||' invalide.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirFic||NomFichier||' ne peut pas etre ouvert.'); DBMS_OUTPUT.PUT_LINE(SQLERRM); WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm); END; -- ---------------------------------------------------------------------------------------------------------------------- --************************************* -- Ecriture du début de suivi du flux --************************************* BEGIN --log BEGIN UTL_FILE.put_line (vo_FileDescLog,'Debut Flux Xml FGenPrel'); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ecriture du fichier Xml' ||DirFic||NomFichier||' : ' || vo_errm); END; -- table de suvi BEGIN SELECT id_w_suivi.NEXTVAL INTO wnumseq_suivi FROM dual; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la lecture de la sequence SUIV' ||DirFic||NomFichier||' : ' || vo_errm); END; BEGIN INSERT INTO RZ_W_SUIVI (IdSuiv,RefFlux,DatDeb,DatFin,CodErr,CodEta) VALUES (wnumseq_suivi,NomFichier,w_sysdate,null,null,w_eta); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''insertion au suivi' ||DirFic||NomFichier||' : ' || vo_errm); END; COMMIT; END; --***************************** -- Récupération du code agence --***************************** BEGIN -- Récupération du type et code formulaire BEGIN SELECT NumTypFo,libFor INTO w_numtypfo,w_libfor FROM RZ_R_FORMULAIRE where numfor=w_numfor; EXCEPTION WHEN NO_DATA_FOUND THEN w_numtypfo:=''; w_libfor:=''; WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr, RefFlux, DatErr, RefClef, CodErr, DesErr) VALUES (wnumseq_err, NomFichier, w_sysdate, null, 'Lecture RZ_R_FORMULAIRE', vo_errm); COMMIT; END; --*************************************************** -- Alimentation de la table de référence RZ_R_REFINS --*************************************************** BEGIN -- Suppression des données en cours sur RZ_R_REFINS BEGIN DELETE FROM RZ_R_REFINS WHERE CodTypRed IN ('AR', 'IR', 'CR'); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err, NomFichier,w_sysdate,NULL, 'Delete RZ_R_REFINS', vo_errm); COMMIT; END; -- Insertion des données en cours sur RZ_R_REFINS pour l'application AR BEGIN w_usermv := 'DRVCSM'; w_codtypred := 'AR'; INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) (SELECT w_codtypred,substr(nom,1,15),null,'02.'||tel, w_mailins FROM RARSECT WHERE usermv=w_usermv); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RARSECT RZ_R_REFINS',vo_errm); COMMIT; END; -- Insertion des données en cours sur RZ_R_REFINS pour l'application AR BEGIN w_usermv := 'PI1'; w_codtypred := 'IR'; INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) (SELECT w_codtypred, substr(nom,1,15),NULL, '02.'||tel, w_mailins FROM RIRSECT WHERE usermv=w_usermv); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RIRSECT RZ_R_REFINS',vo_errm); COMMIT; END; -- Insertion des données en cours sur RZ_R_REFINS pour l'application IR BEGIN w_usermv := 'DRVBDT'; w_codtypred := 'CR'; INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) (SELECT w_codtypred, substr(nom,1,15),null, '02.'||tel, w_mailins FROM RCRSECT WHERE usermv=w_usermv); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RCRSECT RZ_R_REFINS ',vo_errm); COMMIT; END; COMMIT; END; -- recherche de l'année active SELECT MAX(rdannee) INTO w_anndec1 FROM rrdtpre WHERE ccatusa = 'AG' AND cinit = '1'; SELECT MAX(rdannee) INTO w_anndec2 FROM rrdtpre WHERE ccatusa = 'IN' AND cinit = '1'; SELECT MAX(rdannee) INTO w_anndec3 FROM rrdtpre WHERE ccatusa = 'CL' AND cinit = '1'; -- Récupération du numéro d'envoi max SELECT MAX(nenv) INTO w_nenv1 FROM rarsuiv WHERE rdannee=w_anndec1 AND nenv<>9; SELECT MAX(nenv) INTO w_nenv2 FROM rirsuiv WHERE rdannee=w_anndec2 AND nenv<>9; SELECT MAX(nenv) INTO w_nenv3 FROM rcrsuiv WHERE rdannee=w_anndec3 AND nenv<>9; -- Récupération de la date limite de retour des déclarations BEGIN SELECT TO_CHAR(MAX(dretexig), 'YYYY-MM-DD') INTO w_dlimde FROM rarusag a, rarsuiv b WHERE a.rdannee = w_anndec1 AND a.rdannee = b.rdannee AND a.matusa = b.matusa AND a.bcalc = 'DE' AND b.denvdecl IS NOT NULL AND b.dretde IS NULL AND b.nenv = w_nenv1; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; Select id_w_erreur.nextval into wnumseq_err from dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Lecture RARSUIV',vo_errm); COMMIT; END; BEGIN --*********************************************************************-- -- Requete alimentation de la table tempo de stockage des flux GENPREL -- --*********************************************************************-- BEGIN -- insertion resultat 1 INSERT INTO RES_PREL_TEMP (CAPPLI,CODETARE, RESULT1) (SELECT cappli, codetare, XMLElement(name "Formulaire_Entete", XMLElement(name "CodEtaRe", rz_f_genprel.codetaco), XMLElement(name "CodEtaCo", rz_f_genprel.codetare), XMLElement(name "CodBar", rz_f_genprel.codbar), XMLElement(name "NomIns", rz_f_genprel.nomins), XMLElement(name "RefIns", rz_f_genprel.refins), XMLElement(name "TelIns", rz_f_genprel.telins), XMLElement(name "MaiIns", rz_f_genprel.mailins), XMLElement(name "TxtLibDe", rz_f_genprel.txtlibde) ).getClobVal() as Resultat1 FROM rz_f_genprel); /*ORDER BY codetare;*/ EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RES_PREL_TEMP',vo_errm); COMMIT; END; COMMIT; -- insertion resultat 2 BEGIN UPDATE RES_PREL_TEMP alias set RESULT2 = (SELECT qry2.resultat2 FROM (SELECT codetare, XMLAgg(XMLElement(name "FGenPre_Prise_Eau", XMLElement(name "NumPriEa", rz_f_cpt_genprel.numpriea), XMLElement(name "RefAgeCo", rz_f_cpt_genprel.refageco), XMLElement(name "DesOu", rz_f_cpt_genprel.desou), XMLElement(name "ComOu", rz_f_cpt_genprel.comou), XMLElement(name "CodNatRe", rz_f_cpt_genprel.codnatre), XMLElement(name "CodUsa1", rz_f_cpt_genprel.codusa1), XMLElement(name "PctUsa1", 100), XMLElement(name "IndComDa", rz_f_cpt_genprel.indcomda) ) order by rz_f_cpt_genprel.numpriea ).getClobVal() as Resultat2 FROM rz_f_cpt_genprel GROUP BY codetare) qry2 WHERE alias.codetare=qry2.codetare) WHERE ROWID IN (SELECT RES_PREL_TEMP.ROWID FROM RES_PREL_TEMP, (SELECT codetare, XMLAgg(XMLElement(name "FGenPre_Prise_Eau", XMLElement(name "NumPriEa", rz_f_cpt_genprel.numpriea), XMLElement(name "RefAgeCo", rz_f_cpt_genprel.refageco), XMLElement(name "DesOu", rz_f_cpt_genprel.desou), XMLElement(name "ComOu", rz_f_cpt_genprel.comou), XMLElement(name "CodNatRe", rz_f_cpt_genprel.codnatre), XMLElement(name "CodUsa1", rz_f_cpt_genprel.codusa1), XMLElement(name "PctUsa1", 100), XMLElement(name "IndComDa", rz_f_cpt_genprel.indcomda) ) order by rz_f_cpt_genprel.numpriea ).getClobVal() as Resultat2 FROM rz_f_cpt_genprel GROUP BY codetare) qry2 WHERE RES_PREL_TEMP.codetare=qry2.codetare); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD1 RES_PREL_TEMP',vo_errm); COMMIT; END; COMMIT; BEGIN -- insertion resultat 5 UPDATE RES_PREL_TEMP alias set RESULT5 = (SELECT XMLElement(name "Formulaire_Piece_Jointe", XMLElement(name "NumOrdPi", rz_f_genprel.numordpi), XMLElement(name "TypPieJo", rz_f_genprel.typpiejo), XMLElement(name "NomPieJo", rz_f_genprel.nompiejo) ).getClobVal() as resultat5 FROM rz_f_genprel where rownum='1'); EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD5 RES_PREL_TEMP',vo_errm); COMMIT; END; COMMIT; -- insertion resultat 3 BEGIN -- utilisation du curseur -- FOR i IN C_DEP_AG LOOP --w_numdep := i.numdep; w_matricule := i.mat; -- utilisation du curseur -- Open C_FICPT_AG; BEGIN Fetch C_FICPT_AG BULK COLLECT Into tabrec_AG1,tabrec_AG2; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'FETCH1 RES_PREL_TEMP',vo_errm); Commit; EXIT; END; Close C_FICPT_AG; -- insertion de lignes à partir de la collection AG-- BEGIN Forall i in tabrec_AG1.first..tabrec_AG1.last update RES_PREL_TEMP set result3 = tabrec_AG2(i) where RES_PREL_TEMP.codetare=tabrec_AG1(i) and RES_PREL_TEMP.cappli='AG'; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD2-1 RES_PREL_TEMP',vo_errm); Commit; END; COMMIT; END LOOP; -- insertion resultat 4 BEGIN -- utilisation du curseur -- FOR i IN C_DEP_AG LOOP --w_numdep := i.numdep; w_matricule := i.mat; -- utilisation du curseur -- Open C_ACT_AG; BEGIN Fetch C_ACT_AG BULK COLLECT Into tabrec_AG1,tabrec_AG2; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'FETCH4 RES_PREL_TEMP',vo_errm); Commit; EXIT; END; Close C_ACT_AG; -- insertion de lignes à partir de la collection AG-- BEGIN Forall i in tabrec_AG1.first..tabrec_AG1.last update RES_PREL_TEMP set result4 = tabrec_AG2(i) where RES_PREL_TEMP.codetare=tabrec_AG1(i) and RES_PREL_TEMP.cappli='AG'; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD3-1 RES_PREL_TEMP',vo_errm); Commit; END; COMMIT; END LOOP; END; COMMIT; --************************************** -- Requete alimentation du flux GenPrel --************************************** BEGIN BEGIN v_nbrows:=0; /*FOR i IN cur_flux_genprel LOOP v_concat_lob_loc:=v_concat_lob_loc||i.resultat0; v_nbrows:=v_nbrows+1 ; END LOOP;*/ v_lob_loc:=ligne1||ligne2||ligne3||ligne4||w_codage||ligne5||ligne6||w_anndec1||ligne7||ligne8||w_dlimde||ligne9||v_concat_lob_loc||ligne10; EXCEPTION WHEN OTHERS THEN w_eta := 'KO'; vo_errm := SQLERRM; Select id_w_erreur.nextval into wnumseq_err from dual; INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Lecture cur_flux_genprel',vo_errm); COMMIT; END; END; v_amount := 32766; v_offset:=1; -- +-------------------------------------------------------------+ -- | OPENING THE LOB IS OPTIONAL | -- +-------------------------------------------------------------+ BEGIN DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY); EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du LOB' ||DirFic||NomFichier||' : ' || vo_errm); END; BEGIN length := dbms_lob.getlength(v_lob_loc); IF length IS NULL THEN UTL_FILE.put_line (vo_FileDescLog,'LOB NULL' ||DirFic||NomFichier||' : ' || vo_errm); ELSE UTL_FILE.put_line (vo_FileDescLog,'Taille du LOB Nb occurence XML'||DirFic||NomFichier||' : ' || length ||' '||v_nbrows); END IF; END; -- +-------------------------------------------------------------+ -- | WRITE CONTENTS OF THE LOB TO A FILE | -- +-------------------------------------------------------------+ BEGIN j:=1; LOOP BEGIN DBMS_LOB.READ(lob_loc => v_lob_loc, amount => v_amount, offset => v_offset, buffer => v_buffer); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la lecture du LOB' ||DirFic||NomFichier||' : ' || vo_errm); EXIT; END; v_offset := v_offset + v_amount; BEGIN UTL_FILE.PUT_LINE(file => v_out_file, buffer => v_buffer); EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ecriture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm); END; BEGIN UTL_FILE.FFLUSH(file => v_out_file); EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors du fflush1 du fichier ' ||DirFic||NomFichier||' : ' || vo_errm); END; j:=j+v_amount; EXIT WHEN j>=length; END LOOP; BEGIN UTL_FILE.FFLUSH(file => v_out_file); EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors du fflush2 du fichier ' ||DirFic||NomFichier||' : ' || vo_errm); END; BEGIN UTL_FILE.FCLOSE(v_out_file); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirFic||NomFichier||'.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirFic||NomFichier||' invalide.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirFic||NomFichier||' ne peut pas etre ferme.'); WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la fermeture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm); END; -- +-------------------------------------------------------------+ -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT | -- +-------------------------------------------------------------+ BEGIN DBMS_LOB.CLOSE(v_lob_loc); EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la fermeture du LOB ' ||DirFic||NomFichier||' : ' || vo_errm); END; END; -- Cloture au suivi BEGIN IF w_eta='OK' THEN vo_errm :=NomFichier||' longueur:'||length||' Nb lignes:'||v_nbrows; END IF; UPDATE RZ_W_SUIVI SET DatFin=sysdate,CodErr=vo_errm,CodEta=w_eta WHERE idsuiv=wnumseq_suivi; EXCEPTION WHEN OTHERS THEN vo_errm := SQLERRM; UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la mise à jour au suivi' ||DirFic||NomFichier||' : ' || vo_errm); END; COMMIT; -- Fermeture du fichier LOG BEGIN UTL_FILE.fclose (vo_FileDescLog); EXCEPTION WHEN OTHERS THEN RETURN; END; -- END; END Rz_Proc_P_FLUX_GENPREL; /

---

Il faudrait avoir le résultat de la requête sur PGASTAT car la mémoire utilisée par une instance c'est la SGA (en général fixe) + la PGA (variable et qui peut dépasser PGA_AGGREGATE_TARGET). La PGA est utilisée pour les variables PL/SQL (en particulier pour les collections PL/SQL) et pour trier les résultats des requêtes ou pour faire des jointures.

Si on veut limiter la consommation mémoire, il faut limiter l'utilisation des variables PL/SQL ou modifier les requêtes. Vous pouvez aussi essayer de réduire PGA_AGGREGATE_TARGET mais ceci ne peut pas jouer sur la partie PL/SQL et risque de ralentir considérablement les requêtes qui vont utiliser plus d'espace dans le tablespace temporaire (attention à la saturation du tablespace temp. dans ce cas).

Code:

---

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

résultat de la requête sur PGASTAT   aggregate PGA target parameter 536870912 bytes aggregate PGA auto target 431603712 bytes global memory bound 26843136 bytes total PGA inuse 57303040 bytes total PGA allocated 129869824 bytes maximum PGA allocated 895769600 bytes total freeable PGA memory 18808832 bytes PGA memory freed back to OS 1.6334E+10 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 21721088 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 8463360 bytes over allocation count 126587   bytes processed 5.5256E+10 bytes extra bytes read/written 1.4603E+10 bytes cache hit percentage 79.09 percent

---

.

ce que je n'explique pas c'est que sans l'order by sur le XmlAgg() ça fonctionne.
L'order d'une chaine Xml via un substr d'une chaine de caractère qui fait environ 2000 cara est il très gourmand en mémoire ??

Peut être le fetch dans un tableau de clob n'est pas approprié !

je me réponds à moi même ....

Le fetch dans un tableau de clob n'est pas approprié.
consommation énorme de mémoire sur le serveur.

pour résoudre le pb j'ai enlevé le Xmlagg() de mon résultat 3b et j'ai fait un fetch tous simple dans un clob puis j'ai pipé ce clob .
mis à jour ma table de clob.

bilan plus de sort d'une chaine de caractère ni d'aggrégate.

temps de traitement 1 minutes,

merci pour votre aide,

cdt