Comment trouver la date de la dernière modification d'une table ?

Version imprimable

25/07/2013, 14h31
Lung

Comment trouver la date de la dernière modification d'une table ?

Comment trouver la date de la dernière modification d'une table ?

J'ai trouvé la table ALL_TAB_MODIFICATIONS, qui avait l'air de correspondre à ça, mais la table que je cherche n'y figure pas.
Et pourtant, la table en question est modifiée chaque jour (plus d'une fois).
J'ai trié ALL_TAB_MODIFICATIONS par date, et je vais de 2010 à hier. Donc ma table ne peut pas ne pas y figurer.

J'ai raté quelques chose ?
Sinon, existe t'il une autre table pour ce que je cherche ?
25/07/2013, 14h44
Waldar

Vous parlez de modification de données (DML) ou de structure (DDL) ?
25/07/2013, 15h10
Lung

Citation:

Envoyé par Waldar

Vous parlez de modification de données (DML) ou de structure (DDL) ?

De données.
(c'est vrai que j'ai pas précisé) :oops:
25/07/2013, 18h29
Pomalaix

Il y a la pseudo colonne ORA_ROWSCN dans chaque table depuis la V10, mais ce n'est pas très exploitable.
On a dû en parler ici à l'occasion, une recherche sur ce mot-clé devrait vous aider.
26/07/2013, 10h05
pacmann

Bah, pour la date de dernière modification de la table, c'est pas mal max(TIMESTAMP_TO_SCN(ora_rowscn)), non ? (Mis à part que ça peut te renvoyer null :))

Le plus gros problème avec cette fonction, c'est que pour une ligne, on récupère le dernier scn du bloc, et non de la ligne (à moins de forcer la rowdependencie)... mais ce problème ne se pose pas ici...
27/07/2013, 12h45
Pomalaix

Tiens, j'ai retrouvé un sujet où j'avais détaillé la chose :
http://www.developpez.net/forums/d10...e/#post5777588
29/07/2013, 11h11
Lung
Bon, j'ai essayé mais j'obtiens une erreur :

Citation:

Erreur : l'exécution de la requête a échoué !

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

:bug:
Code:

1 2 3 4 --select max(SCN_TO_TIMESTAMP(ORA_ROWSCN)) select ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) from PRODUIT order by ORA_ROWSCN desc
J'ai essayé une autre table (pour vérifier), mais j'obtiens bien la même erreur.
:koi:

En tout cas, je comprend que c'est pas simple ...
29/07/2013, 11h42
FSiebert
Citation:
Envoyé par Lung

Bon, j'ai essayé mais j'obtiens une erreur : :bug:

Code:

1 2 3 4 --select max(SCN_TO_TIMESTAMP(ORA_ROWSCN)) select ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) from PRODUIT order by ORA_ROWSCN desc

J'ai essayé une autre table (pour vérifier), mais j'obtiens bien la même erreur.
:koi:

En tout cas, je comprend que c'est pas simple ...
L'explication de cette erreur est dans le message de Pomalaix dont il a donné le lien juste avant votre message :
Citation:
Envoyé par Pomalaix

2) La fonction SCN_TO_TIMESTAMP fait appel à la table système SMON_SCN_TIME, qui stocke la correspondance entre SCN et temps. (Cette correspondance ne peut pas être obtenue par une formule de calcul, car les SCN et le temps évoluent de manière autonome. Il n'y a donc pas d'autre solution que de stocker régulièrement les valeurs respectives).
Le souci, c'est que SMON_SCN_TIME a une capacité limitée, à ma connaissance non paramétrable, qui serait de 5 jours dans le cas le plus défavorable.

Exemple de modification trop ancienne :

Code:

1 2 3 4 5 select scn_to_timestamp(ora_rowscn) from hr.employees * ERREUR à la ligne 1 : ORA-08181: le numéro indiqué n'est pas un SCN valide ORA-06512: à "SYS.SCN_TO_TIMESTAMP", ligne 1
29/07/2013, 12h00
Lung

Citation:

Envoyé par FSiebert

L'explication de cette erreur est dans le message de Pomalaix dont il a donné le lien juste avant votre message :

Citation:

Envoyé par Pomalaix

Le souci, c'est que SMON_SCN_TIME a une capacité limitée, à ma connaissance non paramétrable, qui serait de 5 jours dans le cas le plus défavorable.

Bin oui, mais dans mon cas, c'est une table qui est modifiée tous les jours (et plein de fois). Ca devrait marcher, non ?
29/07/2013, 12h14
pacmann

Sauf que tu fais la conversion pour toutes les lignes !
Tu peux par exemple écrire une fonction qui encapsule la conversion, zappe l'exception et renvoie null à la place.

Si t'es en Oracle 12c, tu n'as même pas besoin de créer ta fonction :

Code:

1
2
3
4
 
WITH FUNCTION fscnt (rscn in NUMBER) RETURN timestamp IS BEGIN return scn_to_timestamp(rscn); EXCEPTION WHEN OTHERS THEN return null; END;
SELECT max(fsnct(ora_rowscn))
FROM tatable;

29/07/2013, 13h26
FSiebert

Citation:

Envoyé par Lung

Bin oui, mais dans mon cas, c'est une table qui est modifiée tous les jours (et plein de fois). Ca devrait marcher, non ?

Comme expliqué par pacmann, vous appliquez la fonction sur toutes les lignes.

Pourquoi ne pas faire le MAX sur le SCN (qui a priori augmente toujours) ?

Code:

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM ma_table;

Si vous avez besoin de plus d'informations que la MAX, la solution de pacmann est intéressante.

dbms_stats.flush_database_monitoring_info

Bonjour,

Citation:

Envoyé par Lung

Et pourtant, la table en question est modifiée chaque jour (plus d'une fois).
J'ai trié ALL_TAB_MODIFICATIONS par date, et je vais de 2010 à hier. Donc ma table ne peut pas ne pas y figurer.

ALL_TAB_MODIFICATIONS n'est rafraîchie que toutes les 15 minutes (depends de la version d'Oracle). Normal, les infor sont gardées en mémoire et ne vont pas être écrites à chaque exécution.
Il est possible de forcer le rafraîchissement avec: dbms_stats.flush_database_monitoring_info

Exemple:

Code:

1
2
3
4
5
6
7
8
9
10
> create table TEST as select * from dual
table TEST created.
> insert into TEST select * from dual connect by level <= 1000
1,000 rows inserted.
> exec dbms_stats.flush_database_monitoring_info
anonymous block completed
> select * from all_tab_modifications where table_name='TEST'
TABLE_OWNER                                                                                                                      TABLE_NAME                                                                                                                       PARTITION_NAME                                                                                                                   SUBPARTITION_NAME                                                                                                                   INSERTS    UPDATES    DELETES TIMESTAMP            TRUNCATED DROP_SEGMENTS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- -------------------- --------- -------------
FRANCK                                                                                                                           TEST                                                                                                                                                                                                                                                                                                                                                                                                     1000          0          0 29-JUL-13 13:47:14   NO                    0

Par contre, c'est remis à zéro à chaque calcul de stats. Donc ne rien trouver dans ALL_TAB_MODIFICATIONS peut vouloir dire qu'il n'y a pas eu de de modif depuis le dernier calcul de stats.

Cordialement,
Franck.

29/07/2013, 13h54
pacmann

Citation:

Envoyé par Pomalaix

Tiens, j'ai retrouvé un sujet où j'avais détaillé la chose :
http://www.developpez.net/forums/d10...e/#post5777588

Ah tiens, je viens de me souvenir d'un autre cas où ça coince : lors d'un delayed block cleanout, il se peut que le SCN apposé soit estimé à défaut d'avoir le bon (lorsque l'undo correspondant n'est plus dispo)
29/07/2013, 14h17
Waldar

Il faudrait en fait connaître la finalité du besoin afin d'établir la solution qui conviendrait le mieux.
29/07/2013, 14h40
pachot

Bonjour,

Citation:

Envoyé par pacmann

Ah tiens, je viens de me souvenir d'un autre cas où ça coince : lors d'un delayed block cleanout, il se peut que le SCN apposé soit estimé à défaut d'avoir le bon (lorsque l'undo correspondant n'est plus dispo)

ORA_ROWSCN est défini comme the conservative upper bound system change number (SCN) of the most recent change to the row
Ce qui veut dire que la valeur peut être plus récente que la modif.

Tout ce qu'il permet de savoir c'est qu'il n'y a pas eu de modification postérieure au ORA_ROWSCN.

Cordialement,
Franck.
29/07/2013, 15h08
pacmann

Oui Franck, je suis au courant.
Pomalaix décrivait la granularité du SCN était de niveau bloc, sauf rowdependencies ; je précisais simplement qu'il y a d'autres cas où ce n'est pas le commit scn de la ligne.
30/07/2013, 08h28
Lung

Citation:

Envoyé par FSiebert

Pourquoi ne pas faire le MAX sur le SCN (qui a priori augmente toujours) ?

Code:

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM ma_table;

Cette fois, en mettant le max à l'intérieur, j'obtiens bien un résultat (qui à priori est vraissemblable). :ccool:

Citation:

Envoyé par pachot

Par contre, c'est remis à zéro à chaque calcul de stats. Donc ne rien trouver dans ALL_TAB_MODIFICATIONS peut vouloir dire qu'il n'y a pas eu de de modif depuis le dernier calcul de stats.

C'est bon à savoir. ;)
Question bête (je suis pas DBA): Les stats sont calculées à quelle fréquence ? C'est un paramètre ? C'est en fonction d'une action précise ?

Citation:

Envoyé par Waldar

Il faudrait en fait connaître la finalité du besoin afin d'établir la solution qui conviendrait le mieux.

En fait, il semblerait qu'un traitement automatique ne fonctionne plus (très mal conçu : il ne génère pas de log et ne dit pas ce qu'il fait).
Une solution pour savoir si la table en question était bien mise à jour (ou pas) était de connaitre la date de la dernière modification de données.

Maintenant que j'ai enfin un résultat, je vais pouvoir agir en conséquence.
:merci:
30/07/2013, 15h09
pachot

Citation:

Question bête (je suis pas DBA): Les stats sont calculées à quelle fréquence ? C'est un paramètre ? C'est en fonction d'une action précise ?

Ca dépend. Mais le job automatique par défaut va calculer les stats lorsqu'il voit des modifications importantes sur une table (justement en utilisant DBA_TAB_MODIFICATIONS, c'est pour ça qu'il la remet à zéro après)

Du coup:
-> pas de modifs -> rien dans DBA_TAB_MODIFICATIONS
-> beaucoup de modif -> calcul de stats la nuit suivante -> rien dans DBA_TAB_MODIFICATIONS

D'où la difficulté de l'utiliser dans ce cas...

Par contre si LAST_ANALYZED est vieux de 3 mois et qu'il n'y a rien dans DBA_TAB_MODIFICATIONS alors on peut dire qu'il n'y a pas eu de modifs depuis 3 mois.
30/07/2013, 16h15
Lung

Citation:

Envoyé par pachot

Ca dépend. Mais le job automatique par défaut va calculer les stats lorsqu'il voit des modifications importantes sur une table (justement en utilisant DBA_TAB_MODIFICATIONS, c'est pour ça qu'il la remet à zéro après)

Du coup:
-> pas de modifs -> rien dans DBA_TAB_MODIFICATIONS
-> beaucoup de modif -> calcul de stats la nuit suivante -> rien dans DBA_TAB_MODIFICATIONS

D'où la difficulté de l'utiliser dans ce cas...

Par contre si LAST_ANALYZED est vieux de 3 mois et qu'il n'y a rien dans DBA_TAB_MODIFICATIONS alors on peut dire qu'il n'y a pas eu de modifs depuis 3 mois.

:merci: pour ces précisions.