[PL SQL] erreur "Snapshot trop vieux, segment d'annulation..."

**divail** · 19/04/2006, 15h07

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** · 19/04/2006, 15h27

Evite de mettre COMMIT dans les boucles.

**nuke_y** · 19/04/2006, 15h35

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** · 19/04/2006, 15h42

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** · 19/04/2006, 15h50

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** · 19/04/2006, 15h50

[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** · 19/04/2006, 16h26

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** · 19/04/2006, 20h38

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.

**divail** · 20/04/2006, 17h49

Finalement je n'ai pas pu augmenter le UNDO_RETENTION car ou je travaille c'est la version 8.1.7.4
Sinon la dernière solution, bien qu'intéressante, est contraigante. Dans ce cas la autant réaliser un import/export.
Je vais finalement utiliser un script SQL directement sans passer par une procédure.
Merci pour vos solutions.

**nuke_y** · 20/04/2006, 17h54

Mais je ne suis pas complètement sur qu'un script SQL t'empêche d'avoir ce problème là

Pour moi (mais je peux me tromper) c'est le select qui n'est pas conservé suffisement longtemps en mémoire sur le serveur source, donc dans tous les cas...

Mais je peux me tromper, n'hésite pas à venir nous informer.

**pifor** · 20/04/2006, 17h58

Je me demande si vous n'avez pas le problème du delayed block cleanout qui est une variation subtile du ORA-1555. SI c'était le cas, il suffirait dans votre procédure après chaque INSERT massif d'exécuter une commande qui va lire toute la table comme un SELECT * ou un ANALYZE/DBMS_STATS.

Ceci dit, je ne sais pas comment être sûr qu'il s'agit bien de celui là.

Plus de détail ici: http://asktom.oracle.com/pls/ask/f?p...A:275215756923

**divail** · 21/04/2006, 10h34

Finalement n'y arrivant pas via ma procédure, j'ai donc un fait un simple script sql qui fonctionne. D'ailleurs, par exemple, si je fais chacun des "truncate" puis "insert into my_table select..." au fur et à mesure dans sqlplus cela fonctionne (dès lors que je précise "SET TRANSACTION USE ROLLBACK SEGMENT BIGRBS;").

**nuke_y** · 21/04/2006, 10h37

Et si tu ne précises pas tu as quoi ?

**divail** · 21/04/2006, 10h44

J'ai un message d'erreur : "ORA-01562: échec de l'extension du segment d'annulation...". C'est pourquoi je précise d'utiliser un rollback segment qui est plus conséquent.

**pifor** · 21/04/2006, 10h46

Dans ce type de problème, le fait qu'il y ait ou non de l'activité parallèle dans la base de données peut jouer un rôle important puisque toute requête faisant des SELECT -voire des UPDATE ou DELETE - (que ce soit sur les tables sur lesquelles on fait TRUNCATE/INSERT ou sur des tables différentes) peut utiliser des rollbacks segments.
Est-ce que le traitement est lancé toujours dans les mêmes conditions ?

Il serait intéressant de connaître également:
- la configuration globale de vos RBS: combien en avez vous, comment ont-ils été crées/configurés (clause OPTIMAL ou non, clause MAX EXTENTS)
- quel est le volume de chaque table (nombre de lignes, taille du segment)

**divail** · 21/04/2006, 12h06

Pour l'instant je ne peux vous répondre ne pouvant accéder à certaines infos et DBA absent. Je vous tiendrai au courant.

[PL SQL] erreur "Snapshot trop vieux, segment d'annulation..."

Oracle

Discussions similaires

Partager

Partager