Remonter dans le temps avec Flashback Query

La dernière application sur laquelle j'ai travaillé contenait des requêtes basées sur des tables de travail, c'est à dire des tables remplies en début de traitement et supprimées à la fin. Lorsque je devais traiter un problème de performance sur une des requêtes impliquant une ou plusieurs de ces tables je me heurtais au fait que je ne pouvais pas reproduire le problème de performance car ces tables ne contenaient plus les données processées au moment de l'exécution.

Heureusement le Flashback Query m'est venu plusieurs fois en aide.
Le principe du FlashBack Query consiste à requêter une table via une clause SELECT + une clause "AS OF" permettant de préciser à quelle date (Time Stamp) ou à quel Numero SCN on souhaiterait voir l’image de la table.
Imaginons par exemple que mon traitement batch s’exécute entre 4h et 6h du matin et qu’à 9H lors de mon arrivée au boulot (bon ok 10h pour ceux qui me connaissent), je dois analyser pourquoi une requête a mis 30 minutes pour s’exécuter au lieu de 2 minutes. L’idéal serait de pouvoir reproduire le problème de performance sur cette requête mais si les données de ma table de travail ont été supprimées à la fin de mon batch, c’est impossible. L’idée d'utiliser le Flashback Query ici est de pouvoir réinsérer dans cette table les données qu’elle contenait à 4 heures du matin. Selon la taille du tablespace Undo et/ou selon la valeur du paramètre UNDO_RETENTION, il y’a de fortes chances que je puisse récupérer les lignes grâce à la requête suivante :

select * from TABLE_TRAVAIL AS OF TIMESTAMP TO_TIMESTAMP('2011-01-31 04:00:00', 'YYYY-MM-DD HH24:MI:SS');

Exemple:

Comme le PSG a gagné ce week-end je vais me crée une table PSG contenant le numéro, nom et prénom de certains joueurs qui ont fait la gloire de mon club (tiens je viens de perdre des lecteurs marseillais):

SQL> create table PSG (num number, nom varchar2(20), prenom varchar2(20));

Table créée.

SQL> insert into PSG values(1,'LAMA','Bernard');

1 ligne créée.

SQL> insert into PSG values(10,'OKOCHA','Jay-Jay');

1 ligne créée.

SQL> insert into PSG values(9,'PAULETA','Pedro Miguel');

1 ligne créée.

SQL> insert into PSG values(11,'GINOLA','David');

1 ligne créée.

SQL> commit;

Validation effectuée.

SQL> select * from psg;

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David

La table contient 4 lignes.
Je vais maintenant supprimer toutes les lignes de ma table et biens sûr commiter:

SQL> delete psg;

4 ligne(s) supprimée(s).

SQL> commit;

Validation effectuée.

Voilà que j'ai perdu toutes mes lignes :-(

SQL> select * from PSG;

aucune ligne sélectionnée

Grâce au Flashback Query je peux voir les données que contenait cette table avant le DELETE.
La table ayant été supprimé il y'a quelques minutes je peux requêter la table en faisant un SYSTIMESTAMP - 10 minutes:

SQL> select * from PSG AS OF TIMESTAMP ( systimestamp - 10/1440);

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David

Comme par magie j'ai pu retrouver l'image de ma table telle qu'elle était à un moment dans le passé.

Si j'avais voulu requêter la table en précisant une heure précise j'aurais exécuté la requête suivante:

SQL>   select * from PSG AS OF TIMESTAMP TO_TIMESTAMP ('2011-01-31 15:45:00', 'YYYY-MM-DD HH24:MI:SS');

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David

CONCLUSION:
En plus d'épater la galerie le Flashback Query peut vraiment vous être d'une grande aide lors d'un DELETE accidentel ou bien lorsque vous vous voulez analyser un problème de requête en PROD sur des données volatiles.
Pour augmenter les chances de conserver vos données du passé vous pouvez augmenter la taille du tablespace UNDO et augmenter la valeur du paramètre UNDO_RETENTION. Ce dernier permet d'augmenter la durée de rétention des données commitées dans le tablespace UNDO.