Test insert delete

**zico_pro** · 13/06/2008, 12h05

Franchement je n'ai rien compris

Moi je veux faire une suppression apres une insertion, avec la condition que la ligne ne doit pas etre supprimé si elle n'a été pas inserée et inversement,
sans utiliser de trigger parce que je suis limité par un code existant

**zico_pro** · 13/06/2008, 12h06

Le skip locked
prend les enregistrements libres et les bloque en meme temps

**mnitu** · 13/06/2008, 12h08

Envoyé par zico_pro

Le skip locked
prend les enregistrements libres et les bloque en meme temps

Donne moi le lien de la documentation d'ORACLE qui explique SKIP LOCK
J'attends.

**mnitu** · 13/06/2008, 12h12

Envoyé par zico_pro

Franchement je n'ai rien compris

Moi je veux faire une suppression apres une insertion, avec la condition que la ligne ne doit pas etre supprimé si elle n'a été pas inserée et inversement,
sans utiliser de trigger parce que je suis limité par un code existant

Tu veux archiver (= insérer dans une table historique et supprimer de ta table) une partie d’une table T. Pour cella la transaction est (comme je te l'ai déjà dit)

Verrouillage de données à archiver
Insertion dans la table historique
Suppression dans la table T
Commit

Voilà.

**orafrance** · 13/06/2008, 12h21

pour info, voila comment devrait être géré le skip locked selon le support : HOW TO SELECT FROM A TABLE EXCLUDING LOCKED ROWS

Preuve que SKIP LOCKED est à proscrire : Bug 4417341 - SKIP LOCKED mishandles fetch out of sequence errors

Il est buggé et c'est ça qui provoque l'ORA-600

**pifor** · 13/06/2008, 12h40

Envoyé par mnitu

Donne moi le lien de la documentation d'ORACLE qui explique SKIP LOCK
J'attends.

En 11g mais seulement en 11g, l'option SKIP LOCK est documentée dans le SQL Reference:

SKIP LOCKED
SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the database to attempt to lock the rows specified by the WHERE clause and to skip any rows that are found to be already locked. This feature is useful if the goal of the query is to obtain numbers of units, rather than the actual content of the rows.

Note on the WAIT and SKIP LOCKED Clauses
If you specify WAIT or SKIP LOCKED and the table is locked in exclusive mode, then the database will not return the results of the SELECT statement until the lock on the table is released. In the case of WAIT, the SELECT FOR UPDATE clause is blocked regardless of the wait time specified.

**zico_pro** · 13/06/2008, 13h29

Voilà mon code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE
 
 CURSOR  C1 IS
 		  SELECT SC.ROWID FROM tab_orig
          WHERE 
          ...
          for update skip locked;
 
TYPE TAB_UROWID IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
 
l_rowid TAB_UROWID;		  
nbdel number;
 
 
BEGIN
	nbdel := 0;
 
	OPEN  C1;
 
  loop        
		fetch C1 BULK COLLECT into l_rowid LIMIT 1000;
   		if  (l_rowid.COUNT != 0) then
 
			for j in 1..l_rowid.COUNT loop
 
                                      begin
 
                                      insert into tab_archiv
                                             (select ..
                                             from te_orig where rowid = l_rowid(j)
                                             );
 
                                      delete from tab_orig where rowid = l_rowid(j);
 
                                      nbdel := nbdel + 1;
 
                                      exception
                                       when others then
                                       NULL;
                                       end;
 
                                      end loop;
 
 
 
                            end if;
  exit when C1%NOTFOUND;
  end loop;
  commit;
  	CLOSE  C1;
 
EXCEPTION
 
		WHEN OTHERS THEN
   		CLOSE  C_stocouch;
 
END;

Est ce code est juste?
Est ce que le test pour la ligne inserée et supprimée se fait comme ça?

Il est sensé recupérer (par le curseur ) les lignes à supprimer en prenant que les lignes non bloquées et le bloquant penant son traitement, ensuite traiter les lignes par lot de 1000. Pour chaque lot, inserer la ligne dans la table tab_archiv et la supprimer de la table d'origine (tout en respectant l'hypothèse que la ligne n'est supprimé de la table d'origine que si elle a été bien inseré dans la table d'archivage et inversement)

Merci par avance

**orafrance** · 13/06/2008, 13h50

Envoyé par zico_pro

Est ce code est juste?

Est-ce qu'on n'a pas déjà dit que SKIP LOCKED était périlleux ? Est-ce que tu n'as pas une ORA-600 avec ce code ?

**zico_pro** · 13/06/2008, 13h53

Non, en mettant le commit après la boucle du fetch (après avoir traiter tout les enregistrements) je n'ai pas ce message d'erreur

**orafrance** · 13/06/2008, 14h03

enfin dans ton code je ne vois pas ce qui empêche de faire le DELETE si l'INSERT est KO

**zico_pro** · 13/06/2008, 14h04

Mais l'exception là (où j ai mis null) ça suffit pas?

**orafrance** · 13/06/2008, 15h00

si c'est le DELETE qui plante tu insèreras plusieurs fois la même ligne.

Mais il y a mieux à faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE OR REPLACE PROCEDURE archive_lines IS
 CURSOR  C1 IS
 		  SELECT SC.ROWID FROM tab_orig
          WHERE 
          ...
          FOR UPDATE skip locked;
 
TYPE TAB_UROWID IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
 
l_rowid TAB_UROWID;		  
nbdel number;
 
 
BEGIN
	nbdel := 0;
 
	OPEN  C1;
 
  loop        
		fetch C1 BULK COLLECT INTO l_rowid LIMIT 1000;
   		IF  (l_rowid.COUNT != 0) then
 
			FOR j IN 1..l_rowid.COUNT loop
 
                                      begin
                                      INSERT INTO tab_archiv
                                             (SELECT ..
                                             FROM te_orig WHERE rowid = l_rowid(j)
                                             );
                                      exception
                                       when others then
                                       NULL;
                                       end;
 
                                      end loop;
 
 
 
                            end IF;
  exit when C1%NOTFOUND;
  end loop;
  commit;
  	CLOSE  C1;
 
EXCEPTION
 
		WHEN OTHERS THEN
   		CLOSE  C_stocouch;
 
END;

Et ensuite, tu peux lancer autant de fois que tu veux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
EXEC archive_lines;
DELETE FROM tab_orig WHERE pk IN (SELECT pk from tab_archiv);

Tu archives ce qui doit être supprimé et tu supprimes ce qui est archivé. Note que je passe par la PK plutôt que le ROWID pour faire le DELETE.

**zico_pro** · 13/06/2008, 15h07

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
EXEC archive_lines;
DELETE FROM tab_orig WHERE pk IN (SELECT pk FROM tab_archiv);

Merci bien de m'avoir repondu

Mais ce code je le mais apres la fermeture du curseur C1
En plus c'est quoi archive_lines

**mnitu** · 13/06/2008, 15h09

Envoyé par pifor

En 11g mais seulement en 11g, l'option SKIP LOCK est documentée dans le SQL Reference:

Merci pifor, c'est noté!

**orafrance** · 13/06/2008, 15h17

archive_lines c'est la procédure que je crée en lieu et place de ton code

relis ma réponse et réfléchis à ce que ça fait. Si le DELETE tu le fais après la fermeture de C1, j'vois pas bien l'intérêt de le faire aussi dans la boucle