Erreur lors d'éxécution de plusieurs DELETE

Version imprimable

Bonjour,

J'ai un probème, je me suis créé un package avec plusieurs fonctions qui servent à purger des bases de données.

LE code marche trés bien mais j'ai un problème lors de l'éxécution. ça me renvoie une erreur. (pas de souci sur des petits échantillons)

En effet, pendant ma 1ére fonction je fais un delete pour 1 million de lignes environ sur 4 tables différentes et je fais le commit à la fin des 4 delete car si on a une erreur lors du 4éme delete on doit faire un rollback pour les 4 tables. Malheureusement, ça me renvoie une erreur au bout d'1h30 de traitement à cause de l'espace temporaire disponible...

Exemple d'un des 4 delete
Code:

1 2 3 4 5 6 7 8 9 10 11 12 BEGIN DELETE FROM TABLE_PURGE WHERE ID IN (SELECT ID FROM tmp_id); nbPurge := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN outMsgErr := ' ERREUR - DELETE: KO'; ROLLBACK; LogErreur (NOM_TABLE_PURGE, outMsgErr); RETURN -1; END;
Avez-vous une solution optimisé à ce pb? Est-il possible de faire des boucles sur un delete en prenant X valeurs?

Merci d'avance de votre aide car je suis un peu bloqué...

14/03/2014, 10h02
valboubou

ça me renvoit l'erreur suivante lors du 3éme DELETE :
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO01'

Tu peux utiliser un code comme ça, j'espère que les commentaires et les notations sont auto-explicatifs.

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
-- table de test table_test
CREATE TABLE table_test (id NUMBER);
/
 
 
-- peupler la table avec 999 archives
INSERT INTO table_test
SELECT LEVEL 
FROM dual 
CONNECT BY LEVEL < 1000
/
 
 
DECLARE
 -- compteur universel pour déterminer COMMIT temps 
 l_compteur INTEGER := 0;
 
 
 -- compteur pour toutes l'archives efface
 l_lignes_affectées PLS_INTEGER := 0;
 
 -- le numero d'archives touché par un COMMIT operation
 c_étape CONSTANT PLS_INTEGER := 50;
 
 
 -- la procedure efface une archive et efectuer un COMMIT operation 
 PROCEDURE effacer(p_id NUMBER)
 IS
   l_compteur_tmp PLS_INTEGER;
 BEGIN
	 l_compteur := l_compteur + 1;
	 DELETE FROM table_test WHERE ID = p_id;
 
	 l_lignes_affectées := l_lignes_affectées + SQL%ROWCOUNT;
 
	 IF MOD(l_compteur, c_étape) = 0 THEN		 
		 COMMIT;		 				 
		 DBMS_OUTPUT.PUT_LINE('COMMIT ' || c_étape || ' modifications');
	 END IF;	 
 END effacer;
 
BEGIN
	-- boucle principal
	FOR v_rec IN (SELECT ID FROM table_test) LOOP
	  effacer(v_rec.id);
	END LOOP;		
 
	COMMIT;
 
	DBMS_OUTPUT.PUT_LINE('lignes affectées ' || l_lignes_affectées);
END;
/

14/03/2014, 14h10
mnitu

Augmentez la taille de la tablespace ou passez en mode auto-extend.
14/03/2014, 14h14
skuatamad

Citation:

Envoyé par danvoiculet

Tu peux utiliser un code comme ça

Ben non, il n'est pas possible de faire de commit par intervalle puisqu'il est bien spécifié :

Citation:

et je fais le commit à la fin des 4 delete car si on a une erreur lors du 4éme delete on doit faire un rollback pour les 4 tables.

Il vous suffit d'avoir suffisamment d'espace disque disponible et de vérifier la taille et le paramétrage de votre UNDO pour qu'il corresponde à votre besoin.
18/03/2014, 11h09
danvoiculet

Citation:

Envoyé par mnitu

Augmentez la taille de la tablespace ou passez en mode auto-extend.

C'est une très bonne solution, mais tu peux appliquer seulement avec privilège système SYSDBA.

Si tu n'as pas le privilège nécessaire (dans le case où vous êtes un développeur Oracle) tu peux utiliser une code tel que ci-dessous.

Si le code jeter une erreur, cela signifie que certains archives ont été efface et certains archives n'ont pas été effacer. Tu dois exécuter le code encore en fois/plusieurs fois jusqu'à aucun message 'lignes affectées ...' été affiche.
18/03/2014, 11h17
mnitu

Citation:

Si tu n'as pas le privilège nécessaire...

demande l'aide à ton dba.
Commit dans une boucle et delete ligne par ligne, non merci!
18/03/2014, 11h58
valboubou

Merci à tous pour votre aide. C’est très gentil.
Finalement je vais faire un commit après chaque delete mais je vide la table temporaire que si mes 4 delete sont ok si ce n’est pas le cas j’ai crée un code qui reprends la purge au moment de la dernière table.

Maintenant j’aimerai tester mon code en ayant un delete qui revient en erreur… Il y a-t-il une façon simple pour générer à coup sur une erreur dans un delete ?

Merci d’avance.
18/03/2014, 14h34
mnitu

Citation:

Envoyé par valboubou

...
Finalement je vais faire un commit après chaque delete ...

Ceci n'est peut être pas la meilleure idée ...
18/03/2014, 14h50
valboubou

pourquoi?
Je n'ai pas la possibilité d'augmenter le tablespace...
Là je sélectionne les ID à supprimer des 4 tables, je les insert dans une table.
Je fais mon traitement de purge. Si pas d'erreur dans les delete la table des ID est vidé à la fin.
Un commit est réalisé à chaque delete mais si un des 4 delete plantent j'alimente une table d'anomalie av la date et la table en erreur. La table des ID n'est pas vidée.
Dans mon code, je regarde au début du traitement si ma table des ID est vide, si oui je réinsert des données sinon je vais purger les données qu'il me reste à purgerde la derniére fois.
Une solution plus adéquate?
Sinon pour tester si mon code marche bien j'aimerai faire échouer un delete... y-a til une solution facile?
Merci beaucoup.
18/03/2014, 15h28
Ahmed AANGOUR

A chaque fois que vous committez vous générez du travail supplémentaire pour la base.
De tête je dirais que:
- log Writer doit écrire le contenu du log buffer sur disque
- les locks sont relâchés
- un block cleanout est effectué pour les blocks modifiés

En gros en commitant à chaque ligne vous allez ralentir votre purge. Ca peut être aussi impactant si vous avez d'autres sessions actives qui tournent au même moment.
Qu'est-ce qui vous empêche de demander une augmentation de la taille de l'undo?
18/03/2014, 16h15
mnitu
Citation:

Envoyé par valboubou

...
Là je sélectionne les ID à supprimer des 4 tables, je les insert dans une table.
Je fais mon traitement de purge. Si pas d'erreur dans les delete la table des ID est vidé à la fin.
Un commit est réalisé à chaque delete mais si un des 4 delete plantent j'alimente une table d'anomalie av la date et la table en erreur. La table des ID n'est pas vidée.
Dans mon code, je regarde au début du traitement si ma table des ID est vide, si oui je réinsert des données sinon je vais purger les données qu'il me reste à purgerde la derniére fois.
Une solution plus adéquate?
...

Oui
Code:

1 2 3 4 5 delete tab1 where ...; delete tab2 where ...; delete tab3 where ...; delete tab4 where ...;
Pour faire échouer le delete:
- supprimer le parent dans une clé étrangère quand des enregistrements fils existent (ça dépende)
- forcer une mauvais coercition par Oracle
- retourner une valeur dans une variable mal taillé
- déclencher une erreur dans un trigger

Je crois que vous avez mal compris sa réponse, quand il dis faire un commit à chaque delete, ce n'est pas ligne par ligne, cf son 1er post.
En gros il fait:
Code:

1 2 3 4 5 6 7 8 DELETE tab1 WHERE ID IN (SELECT ID FROM tmp_id); Commit; DELETE tab2 WHERE ID IN (SELECT ID FROM tmp_id); Commit; DELETE tab3 WHERE ID IN (SELECT ID FROM tmp_id); Commit; DELETE tab4 WHERE ID IN (SELECT ID FROM tmp_id); Commit;