Discussion :

[divail]

J'ai réalise la procédure ci-dessous sous Oracle afin de supprimer le contenu de certaines tables et de les mettre à jour à partir du contenu des tables correspondantes provenant d'un schéma d'une autre base.

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
 
PROCEDURE MAJ_DATA_MAELIS  IS
 
CURSOR liste_tables_cursor IS
SELECT table_name
FROM user_tables;
 
req_delete VARCHAR2(4000);
req_insert VARCHAR2(4000);
 
err_msg VARCHAR2(80):=null;
err_num NUMBER:=0;
 
BEGIN
 
FOR liste_tables_rec IN liste_tables_cursor
  LOOP
    req_delete := 'TRUNCATE TABLE ' || liste_tables_rec.table_name;
    req_insert := 'INSERT INTO ' || liste_tables_rec.table_name || ' SELECT * FROM NOM_SCHEMA.'|| liste_tables_rec.table_name || '@BASE';
 
    BEGIN
      EXECUTE IMMEDIATE req_delete;
      commit;
      SET TRANSACTION USE ROLLBACK SEGMENT BIGRBS;
      EXECUTE IMMEDIATE req_insert;
      commit;
      SET TRANSACTION USE ROLLBACK SEGMENT BIGRBS;
      DBMS_OUTPUT.put_line('la table ' || liste_tables_rec.table_name || ' a été mise à jour');
    EXCEPTION
      WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 80);
        dbms_output.put_line( 'Err num = ' || to_char(err_num) );
        dbms_output.put_line( 'Err msg = ' || err_msg );
    END;
  END LOOP;
END maj_data_maelis;

Seules les 12ères tables sur 16 ont correctement été mises à jour. Je recois les erreurs suivantes:

ORA-01555: Snapshot tros vieux, segment d'annulation 1, nommé "BIGRBS" tros petit
ORA-06512: à "MON_SCHEMA.MA_PROCEDURE", ligne 16
ORA-06512: à ligne 2

Pourtant la taille du rollback segment a bien été augmentée et j'ai ajouté les lignes comme "commit;" et "SET TRANSACTION USE ROLLBACK SEGMENT BIGRBS;" pour gérer ce problème mais je ne trouve pas de solution.
D'ou cela peut-il provenir?
D'avance merci.

[bouyao]

Evite de mettre COMMIT dans les boucles.

[nuke_y]

Combien de temps a duré ton traitement ?

Alors de ce que j'en sais (puisque je ne peux pas utiliser la fonction Rechercher) tu as un paramètre UNDO_RETENTION qui indique combien de temps (en secondes je crois) un snapshot (une photo à un instant t) de tes données doit être conservé.

Si tu as un curseur par exemple qui boucle sur ce snapshot et qu'il met trop longtemps, un UNDO_RETENTION trop petit fera que ton snapshot est supprimé avant que tu ais finis de l'utiliser.

De la même façon, même si tu as un UNDO_RETENTION de 24h, si un autre utilisateur a besoin d'espace temporaire pour y mettre un snapshot et que ton snapshot est indiqué comme commité, ton snapshot sera remplacé par celui de l'autre USER.

Donc les solutions :
- Ne faire un commit qu'à la fin de la transaction et pas par petits bouts.
- avoir un énorme UNDOTBS
- ne pas faire plusieurs gros traitements parallèlles
- augmenter le UNDO_RETENTION (mais ça ne réglera pas le problème de la concurrence).

Ici un lien sympa sur le sujet : http://www.ixora.com.au/tips/admin/ora-1555.htm

[pifor]

Je crois que dans ce cas là, supprimer les COMMIT ne change rien car il y a un TRUNCATE qui fait automatiquement un COMMIT.

[divail]

J'avais mis un commit a chaque étape car je pensais que cela permettrait de "liberer" le rollback segment...donc je vais revenir a ne mettre qu'un commit a la fin.
Sinon oui ma transaction est assez longue.
Avec un unique commit, la procedure n'arrive toujours qu'a la 12ème table / 16 et j'obtiens les erreurs suivantes:

Err num = -1453
Err msg = ORA-01453: SET TRANSACTION doit être la première instruction de la transaction

Je vais essayer de mettre le "set transaction" au début.
Et je vais regarder du coté des "UNDOTBS" et "UNDO_RETENTION".

[nuke_y]

[Parenthèse]Je crois que c'est pire : le truncate ne se pose MEME PAS la question du commit.[/Parenthèse]

Je pencherais donc plutôt pour un délais d'exécution trop grand. En gros tu mets trop de temps à faire ton select puis ton insert et la base dans laquelle tu fais le select te vire. Il faudrait augmenter le UNDO_RETENTION dans la base source ou tenter un export/import plutôt.

ATTENTION mes conseils sont à prendre avec des pincettes !!

[divail]

Si je mets un seul commit et que je mets le "set transaction" au début, il ne me prends pas le rollback segment que je souhaite et j'obtiens des erreurs pour les tables a gros volume (la 3eme et la 4eme) :

Err num = -1562
Err msg = ORA-01562: échec de l'extension du segment d'annulation 6
ORA-01628: # max d'ens

Je vais essayer d'augmenter le UNDO_RETENTION.

[pifor]

Il y a une solution différente qui consiste à replacer TRUNCATE TABLE et INSERT INTO ... SELECT * FROM ... par:

- supprimer les contraintes clés étrangères pour la table à supprimer
- DROP TABLE
- CREATE TABLE ... AS SELECT * FROM ...
- recréer les index
- recréer les contraintes clés étrangères
- recréer les GRANTS
- recréer les synonymes

L'inconvénient de cette méthode est d'avoir beaucoup de DDL dans du PL/SQL mais les 2 avantages sont la rapidité d'exécution et l'utilisation réduite des rollback segments.