Ne serait-ce que par esprit de contradiction, le premier avril est le parfait moment pour aborder le sujet le moins drôle de l'univers :voir les données avant modifications, et les comparer aux données présentes !
0) Un jeu d'essai simple :
SQL> CREATE TABLE testflash AS
2 SELECT level id, mod(level, 5) n
3 FROM DUAL
4 CONNECT BY level <= 2000;
Table créée
... et quelques modifications :
SQL> UPDATE testflash
2 SET n = 99
3 WHERE id = 1000;
1 ligne mise à jour.
SQL> INSERT INTO testflash VALUES(2001, 99);
1 ligne créée.
SQL> DELETE FROM testflash WHERE id = 2000;
1 ligne supprimée.
SQL> commit;
Validation effectuée.
On vérifie que les modifications sont bien là :
SQL> SELECT *
2 FROM testflash
3 WHERE id IN (1000, 2000, 2001);
ID N
---------- ----------
1000 99
2001 99
1) AS OF TIMESTAMP :
Par principe, Oracle est capable de livrer une vue dans le passée des données, afin de garantir la consistence de la lecture. C'est fait de manière automatique, par exemple lorsque ta requête ultra longue ne voit pas les modifications qui sont commitées par ailleurs depuis le début de son executions.
En 9i FLASHBACK QUERY permet d'exploiter la mécanique pour demander explicitement, à un instant donné, de lire les données du passé. (Je passe les détails parce ça m'intéresse pas plus que ça)
Et en 9iR2, on va un peu plus loin en permettant, à l'intérieur d'une requête, de préciser quelles tables doivent être lues dans le passé. C'est la clause AS OF TIMESTAMP ajoutée après le nom de la table :
SQL> SELECT *
2 FROM testflash AS OF TIMESTAMP sysdate -
1/24/12
3 WHERE id IN (1000, 2000, 2001);
ID N
---------- ----------
1000 0
2000 0
Tadam ! Ce sont les données avant modifications.
1/24/12 pour dire que c'était "il y a 5 minutes", mais on peu bien sûr spécifier directement une vraie date (et c'est ce qu'on va faire dans la suite) :
SQL> SELECT *
2 FROM testflash AS OF TIMESTAMP to_date('20120401 13:00',
'yyyymmdd hh24:mi')
3 WHERE id IN (1000, 2000, 2001);
ID N
---------- ----------
1000 0
2000 0
2) Full outer Compare !
Et donc maintenant, pour voir exactement ce qui a changé, une jointure full externe sur la table avant / après permet de faire un diff sympa :
SQL> SELECT CASE WHEN a.id is NULL THEN 'added'
2 WHEN b.id IS NULL
THEN 'removed'
3 ELSE 'modified' END status,
4 a.id, a.n, b.id newid, b.n newn
5 FROM testflash a
6 FULL OUTER JOIN testflash AS OF TIMESTAMP
to_date('20120401 13:00', 'yyyymmdd hh24:mi') b
7 ON a.id = b.id
8 WHERE a.id IS NULL OR b.id IS NULL OR a.n <> b.n;
STATUS ID
N NEWID NEWN
-------- ---------- ---------- ---------- ----------
modified 1000 99
1000 0
removed 2001 99
added
2000 0
3) Entre autres
Il y a quelques pré-requis :
- ëtre post 9.2 bien sûr
- l'utilisateur doit avoir le grant execute sur le package dbms_flashback
- les données doivent être disponibles, un peu dans l'esprit ORA-1555
- pas possible lorsqu'il y a eu un DDL entre temps sur la table (notamment TRUNCATE !)
TIens sinon, la semaine dernière chuis passé OCM !
(On n'échappe pas aux traditions stupides...)
Allez, zou, je vais aller m'occuper de ma gamine qui a bu entre temps toute l'eau de Javel...
(Pas la peine d'appeler la DASS, c'est encore un poisson, hein !)
Derniers Commentaires