La pseudo-colonne ORA_ROWSCN et les dates de mise à jour d'une table - ORA_ROWSCN and the update dates of a table
Introduction
La pseudo-colonne ORA_ROWSCN permet d'associer un SCN (System Change Number) à la dernière modification d'un bloc de données ou d'une ligne d'une table. Cette colonne permet notamment de répondre à la question : quand cet enregistrement a-t-il été mis à jour la dernière fois?
Points d'attention
Par défaut, c'est au niveau bloc que cette pseudo-colonne est associée, pas au niveau ligne.
Base de tests
Une base Oracle 12.
Exemples
============================================================================================
Table avec l'option NOROWDEPENDENCIES : SCN niveau bloc
============================================================================================
Créons une table avec spécifiquement la valeur par défaut pour cette fonctionnalité : NOROWDEPENDENCIES. De la sorte, toutes les lignes d'un même bloc auront la même valeur ORA_ROWSCN.
SQL> create table zz01 (id number, nom varchar2(50 CHAR), prenom varchar2(50 CHAR)) norowdependencies;
Table ZZ01 created.
On vérifie la valeur.
SQL> SELECT table_name, dependencies
FROM user_tables
WHERE table_name like 'ZZ%';
TABLE_NAME DEPENDENCIES
-------------------------
ZZ01 DISABLED
On insère 10 000 enregistrements pour avoir, pour cette table, plus de un bloc de données.
SQL> begin
for i in 1 .. 10000 loop
insert into zz01 values (i, 'TOM' || to_char(i), 'FRED' || to_char(i));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
La table a pour le moment un seul ORA_ROWSCN car c'est lors du COMMIT que tous les INSERTs ont été validés, donc qu'un seul SCN a été calculé pour tous les blocs de la table.
SQL> select distinct ora_rowscn from zz01;
ORA_ROWSCN
-----------
11109129
Avec le package DBMS_ROWID, il est clair que les données sont réparties dans plusieurs blocks : n°9295 pour les premiers enregistrements, N°9329 pour les derniers.
SQL> select Z.*,
rowid,
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) AS "File ID",
DBMS_ROWID.ROWID_OBJECT(rowid) AS "Object ID",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS "Block ID",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS "Row Number",
ora_rowscn,
to_char(scn_to_timestamp(ora_rowscn), 'DD/MM/YYYY HH24:MI:SS') AS "Date MAJ"
from zz01 Z
where Z.id < 4 or Z.id > 9997
order by Z.id;
ID NOM PRENOM ROWID File ID Object ID Block ID Row Number ORA_ROWSCN Date MAJ
-------------------------------------------------------------------------------------------------------------------
1 TOM1 FRED1 AAAT6IAAMAAACRPAAA 12 81544 9295 0 11109129 28/07/2019 07:01:08
2 TOM2 FRED2 AAAT6IAAMAAACRPAAB 12 81544 9295 1 11109129 28/07/2019 07:01:08
3 TOM3 FRED3 AAAT6IAAMAAACRPAAC 12 81544 9295 2 11109129 28/07/2019 07:01:08
9998 TOM9998 FRED9998 AAAT6IAAMAAACRxACQ 12 81544 9329 144 11109129 28/07/2019 07:01:08
9999 TOM9999 FRED9999 AAAT6IAAMAAACRxACR 12 81544 9329 145 11109129 28/07/2019 07:01:08
10000 TOM10000 FRED10000 AAAT6IAAMAAACRxACS 12 81544 9329 146 11109129 28/07/2019 07:01:08
6 rows selected.
On met à jour les enregistrements d'id 1 et 2, dans le même bloc, avec un COMMIT après chaque UPDATE, pour calculer un ORA_ROWSCN différent.
SQL> update zz01 set NOM = 'DURAND' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update zz01 set NOM = 'JOHN' where id = 2;
1 row updated.
SQL> commit;
Comit complete.
Que voit-on? Les ORA_ROWSCN de l'id 1 et 2 sont identiques (c'est celui de l'UPDATE 2 qu'on voit) : on a donc bien une mise à jour de niveau bloc et pas de niveau ligne avec l'option par défaut NOROWDEPENDENCIES. A noter que le ORA_ROWSCN du dernier bloc lui n'a pas été modifié.
SQL> select Z.*,
rowid,
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) AS "File ID",
DBMS_ROWID.ROWID_OBJECT(rowid) AS "Object ID",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS "Block ID",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS "Row Number",
ora_rowscn,
to_char(scn_to_timestamp(ora_rowscn), 'DD/MM/YYYY HH24:MI:SS') AS "Date MAJ"
from zz01 Z
where Z.id < 4 or Z.id > 9997
order by Z.id;
ID NOM PRENOM ROWID File ID Object ID Block ID Row Number ORA_ROWSCN Date MAJ
--------------------------------------------------------------------------------------------------------------
1 DURAND FRED1 AAAT6IAAMAAACRPAAA 12 81544 9295 0 11112582 28/07/2019 07:05:46
2 JOHN FRED2 AAAT6IAAMAAACRPAAB 12 81544 9295 1 11112582 28/07/2019 07:05:46
3 TOM3 FRED3 AAAT6IAAMAAACRPAAC 12 81544 9295 2 11112582 28/07/2019 07:05:46
9998 TOM9998 FRED9998 AAAT6IAAMAAACRxACQ 12 81544 9329 144 11109129 28/07/2019 07:01:08
9999 TOM9999 FRED9999 AAAT6IAAMAAACRxACR 12 81544 9329 145 11109129 28/07/2019 07:01:08
10000 TOM10000 FRED10000 AAAT6IAAMAAACRxACS 12 81544 9329 146 11109129 28/07/2019 07:01:08 6 rows selected.
============================================================================================
Table avec l'option ROWDEPENDENCIES : SCN niveau ligne
============================================================================================
On va maintenant tester l'option ROWDEPENDENCIES.
Ah, ça commence mal, impossible de faire un ALTER TABLE pour forcer la table en ROWDEPENDENCIES.
SQL> alter table zz01 ROWDEPENDENCIES;
Error starting at line : 1 in command -
alter table zz01 ROWDEPENDENCIES
Error report -
ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
*Cause:
*Action:
L'idéal serait d'utiliser le package DBMS_REDEFINITION mais c'est hors scope de cet article, je vais donc créer une autre table.
ATTENTION, il y a un piège avec le CTAS (Create Table As Select) et la modification d'un paramètre : pas de message d'erreur sous SQLcl mais le paramètre n'est pas pris en compte.
SQL> create table ZZ02 as SELECT * FROM ZZ01 rowdependencies;
Table ZZ02 created.
SQL> SELECT table_name, dependencies
FROM user_tables
WHERE table_name like 'ZZ%';
TABLE_NAME DEPENDENCIES
-------------------------
ZZ01 DISABLED
ZZ02 DISABLED
On recommence MAIS avec un vrai CREATE TABLE cette fois :-)
SQL> create table zz03 (id number, nom varchar2(50 CHAR), prenom varchar2(50 CHAR)) rowdependencies;
Table ZZ03 created.
YES, cette fois c'est OK!
SQL> SELECT table_name, dependencies
FROM user_tables
WHERE table_name like 'ZZ%';
TABLE_NAME DEPENDENCIES
--------------------------
ZZ01 DISABLED
ZZ02 DISABLED
ZZ03 ENABLED
On insère à nouveau 10 000 enregistrements.
SQL> begin
for i in 1 .. 10000 loop
insert into zz03 values (i, 'TOM' || to_char(i), 'FRED' || to_char(i));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select distinct ora_rowscn from zz03;
ORA_ROWSCN
-----------
11117681
SQL> select Z.*,
rowid,
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) AS "File ID",
DBMS_ROWID.ROWID_OBJECT(rowid) AS "Object ID",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS "Block ID",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS "Row Number",
ora_rowscn,
to_char(scn_to_timestamp(ora_rowscn), 'DD/MM/YYYY HH24:MI:SS') AS "Date MAJ"
from zz03 Z
where Z.id < 4 or Z.id > 9997
order by Z.id;
Il y a bien plusieurs blocs pour cette table.
ID NOM PRENOM ROWID File ID Object ID Block ID Row Number ORA_ROWSCN Date MAJ
--------------------------------------------------------------------------------------------------------------
1 TOM1 FRED1 AAAT6QAAMAAACSvAAA 12 81552 9391 0 11117681 28/07/2019 07:16:11 2 TOM2 FRED2 AAAT6QAAMAAACSvAAB 12 81552 9391 1 11117681 28/07/2019 07:16:11 3 TOM3 FRED3 AAAT6QAAMAAACSvAAC 12 81552 9391 2 11117681 28/07/2019 07:16:11 9998 TOM9998 FRED9998 AAAT6QAAMAAACTaACB 12 81552 9434 129 11117681 28/07/2019 07:16:11 9999 TOM9999 FRED9999 AAAT6QAAMAAACTaACC 12 81552 9434 130 11117681 28/07/2019 07:16:11 10000 TOM10000 FRED10000 AAAT6QAAMAAACTaACD 12 81552 9434 131 11117681 28/07/2019 07:16:11
6 rows selected.
On update les deux premières lignes.
SQL> update zz03 set NOM = 'TEST01' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update zz03 set NOM = 'TEST02' where id = 2;
1 row updated.
SQL> commit;
Commit complete.
BINGO! On a bien deux ORA_ROWSCN différents pour l'id 1 et 2 alors qu'ils sont dans le même bloc :-) Nous avons bien maintenant une table avec une mise à jour de l'ORA_ROWSCN ligne par ligne. On peut donc maintenant tracer les dates des dernières mises à jour des données.
SQL> select Z.*,
rowid,
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) AS "File ID",
DBMS_ROWID.ROWID_OBJECT(rowid) AS "Object ID",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS "Block ID",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS "Row Number",
ora_rowscn,
to_char(scn_to_timestamp(ora_rowscn), 'DD/MM/YYYY HH24:MI:SS') AS "Date MAJ"
from zz03 Z
where Z.id < 4 or Z.id > 9997
order by Z.id;
ID NOM PRENOM ROWID File ID Object ID Block ID Row Number ORA_ROWSCN Date MAJ
--------------------------------------------------------------------------------------------------------------
1 TEST01 FRED1 AAAT6QAAMAAACSvAAA 12 81552 9391 0 11117740 28/07/2019 07:17:53
2 TEST02 FRED2 AAAT6QAAMAAACSvAAB 12 81552 9391 1 11117744 28/07/2019 07:18:02
3 TOM3 FRED3 AAAT6QAAMAAACSvAAC 12 81552 9391 2 11117681 28/07/2019 07:16:11
9998 TOM9998 FRED9998 AAAT6QAAMAAACTaACB 12 81552 9434 129 11117681 28/07/2019 07:16:11
9999 TOM9999 FRED9999 AAAT6QAAMAAACTaACC 12 81552 9434 130 11117681 28/07/2019 07:16:11
10000 TOM10000 FRED10000 AAAT6QAAMAAACTaACD 12 81552 9434 131 11117681 28/07/2019 07:16:11
6 rows selected.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)