Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
1 avril 2012 7 01 /04 /avril /2012 14:02

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 !)

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Pacman FPS 17/03/2013 19:15

En fait il s'agit de comparer les données après même avoir fait un commit, est-ce grâce aux logs ? (je ne connais pas vraiment Oracle, plus MySQL et Postgres).
Pour Pacman FPS, c'est pas mal tu visualises par l'intermédiaire de ses yeux, ce qui change vraiment la dimension des choses lorsque tu joues :)

Pacman 20/03/2013 07:23



Oui c'est le principe, même si ce ne sont pas tout à fait des logs. "L'undo" est le mécanisme de base sous Oracle qui permet deux choses : rollback des données et lecture consistante (En gros,
c'est un versionning des données qui est effectué à la modification).


Là où ce n'est pas une log, c'est que c'est une donnée qui se perd, car l'espace qui est réservé pour tout l'undo est limité (donc tu réutilises les emplacements de ceux qui sont COMMIT, du plus
vieux au plus récent)... et une fois que ce n'est plus dispo, le AS OF TIMESTAMP te crache une erreur "snapshot too old".


Je vais peut essayer le Pacman FPS, ça a l'air fun :)



Pacman FPS 10/03/2013 11:02

Je n'ai pas tout compris ^^ mais bravo !

Pacman 17/03/2013 13:12



Ben du coup bravo pour quoi ? Enfin merci quand même, ça fait toujours plaisir :)


Sinon, Pacman FPS est un joli pseudo, j'imagine bien pacman avec un M16 taper sur des fantômes (pas sûr cependant que ça leur fasse mal) :)



Laurent Schneider 02/04/2012 13:09

Ca me rappelle que ca fait 5 ans que l'email sur otn.oracle.com/ocm n'est pas valide. Bon, tout ça de spams en moins :-)

Laurent Schneider 01/04/2012 17:46

bon, l'eau de javel, pas top pour la gamine, mais t'es vraiment passer OCM ou c'est aussi un poisson?

Félicitations :)

Pacman 02/04/2012 11:12



Laurent, j'ai même demandé à ce que je sois inséré juste après toi dans la liste :


http://i.imgur.com/HHLRp.png


Merci :)