Home Oracle / Teoría/Conocimiento ORA_ROWSCN ¿Cómo conocer la fecha y hora de última modificación de una fila dentro de una tabla?

ORA_ROWSCN ¿Cómo conocer la fecha y hora de última modificación de una fila dentro de una tabla?

Cele Cueva

Ayer me tocó afrontar una tarea sencilla, de las del día a día. Algo que para cualquier DBA es de primero de primaria. Borrar unas filas de una tabla.

Aparentemente, algo muy sencillo.

Primero, una select para ver lo que hay en la tabla
Segundo, montamos una select para seleccionar lo que vamos a borrar y estar seguros antes de ejecutar el delete.
Tercero, como uno es inseguro por naturaleza, hace una copia de la tabla por si acaso (create table NOMBRETABLA as select * from TABLAORIGEN).
Cuarto, ya preparados y asegurados, ejecutamos el delete.
Quinto, un commit, que confirme bien que hemos borrado los registros.
Sexto y último paso, volver a hacer el primer select para verificar que hemos borrado los datos.

Fácil no? Una tarea rápida, de las que a veces se agradecen para descansar un poco la cabeza y no tener que pensar demasiado.

Hoy, me dicen si realmente he ejecutado el borrado, que parece que los registros no se han borrado, que siguen ahí.

What? Como es posible? 😱 Juro y perjuro que los he borrado.

Compruebo, y efectivamente, ahí están. NO ES POSIBLE. Los registros siguen en la tabla. 😡

Repito el mismo procedimiento, vuelvo a montar las consultas (porque algo tan simple, para qué lo iba a guardar?) ejecuto todo, vuelvo a comprobar, y los datos, efectivamente, ya no están.

Antes de confirmar de nuevo que los he vuelto a borrar, he preferido ser más conservador y esperar.

TACHÁN!! Al esperar un rato, vuelvo a comprobar, y ahí están de nuevo, los registros han vuelto a aparecer en la tabla.

El Pensamiento

Lo primero que he pensado: ¿Habrá alguna forma de ver la fecha y hora de la última modificación de la tabla? ¿del datafile? y si fuéramos más allá, ¿de cada una de las filas que he borrado y han vuelto a aparecer?

Haciendo una búsqueda por la web, me he topado con el concepto ORA_ROWSCN.

Me ha dado por leer un poco sobre él y conocer la teoría de esta funcionalidad.

La Teoría

ORA_ROWSCN es una pseudocolumna que devuelve el número de cambio del sistema (SCN) del cambio más reciente de cada fila. Nos puede ser de utilidad para conocer aproximadamente cuándo se actualizó una fila por última vez.
Este dato no es preciso al 100%, ya que Oracle hace un seguimiento del SCN por cada transacción confirmada que hay en el bloque donde reside la fila que estamos consultando, pero si bastante aproximado.

Esta pseudocolumna tiene algunas particularidades:

NO se puede usar en la consulta a una vista
NO es compatible con Flashback Query
NO es compatible con tablas externas

SI se puede usar para hacer referencia a la tabla subyacente al crear la vista
SI se puede usar con un WHERE cuando realizamos un UPDATE o un DELETE

En caso de que queramos que este dato sea mucho más exacto, siempre podremos habilitar ROWDEPENDENCIES al crear la tabla. Esto activa el seguimiento a nivel de fila. Pero 👀 ¡ojo! 👀, esto aumentará en 6 bytes el tamaño de cada fila. (O eso dice la documentación)

Si queremos conocer si ROWDEPENDENCIES está activado, lo podemos hacer con la siguiente consulta:

SELECT owner, table_name, dependencies FROM dba_tables;

Ejemplos

Vamos a crear dos tablas, y vamos a insertar datos, en diferente momento, para ver que la marca de tiempo es diferente.
Una tabla la vamos a crear por defecto, y otra con ROWDEPENDENCIES.
Después vamos a ver qué contiene la pseudocolumna ORA_ROWSCN y como convertirlo a un formato de fecha/hora entendible para humanos.

-- Creamos una tabla e insertamos 6 registros. 

17:07:24 SQL> create table TABLA1(numero number,letra varchar(5));
Tabla creada.

17:08:54 SQL> insert into TABLA1 (numero, letra) values (1,'a');
17:08:54 SQL> insert into TABLA1 (numero, letra) values (2,'b');
17:08:54 SQL> insert into TABLA1 (numero, letra) values (3,'c');
17:08:54 SQL> insert into TABLA1 (numero, letra) values (4,'d');
17:08:54 SQL> insert into TABLA1 (numero, letra) values (5,'e');
17:08:54 SQL> insert into TABLA1 (numero, letra) values (6,'f');
1 fila creada. 
1 fila creada.
1 fila creada.
1 fila creada.
1 fila creada.
1 fila creada.
17:08:55 SQL> commit;
Confirmacion terminada.

Ahora viene el momento de consultar la pseudocolumna ORA_ROWSCN.

17:09:28 SQL> select to_char(ORA_ROWSCN),numero,letra  from TABLA1; 

TO_CHAR(ORA_ROWSCN)       NUMERO LETRA
--------------------- ---------- -----
13506912977168                 1 a
13506912977168                 2 b
13506912977168                 3 c
13506912977168                 4 d
13506912977168                 5 e
13506912977168                 6 f

Como podemos ver, la columna contiene un número, pero no una fecha/hora que podamos entender. Ahora procedemos a convertir este dato, para que sea comprensible por humanos con la función scn_to_timestamp.

17:13:53 SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
17:13:53 SQL> select scn_to_timestamp(13506912977168) as timestamp from dual;

TIMESTAMP
-----------------------------
06/09/24 17:08:55,000000000

También podemos obtener la conversión junto con todas las filas de la tabla, por si hay varios valores de SCN distintos, podamos verlos directamente cada uno en su fila.

select scn_to_timestamp(to_char(t.ORA_ROWSCN)) as timestamp, t.numero, t.letra from dual, TABLA1 t;

TIMESTAMP                         NUMERO LETRA
----------------------------- ---------- -----
06/09/24 17:08:55,000000000            1 a
06/09/24 17:08:55,000000000            2 b
06/09/24 17:08:55,000000000            3 c
06/09/24 17:08:55,000000000            4 d
06/09/24 17:08:55,000000000            5 e
06/09/24 17:08:55,000000000            6 f

Creamos la segunda tabla, pero esta, añadiendo la opción ROWDEPENDENCIES. Verificamos la diferencia entre las dos tablas e insertamos también 6 registros.

17:20:43 SQL> create table TABLA2(numero number,letra varchar(5)) ROWDEPENDENCIES;
Tabla creada.


17:23:52 SQL> select table_name, dependencies from dba_tables where table_name in ('TABLA1','TABLA2');

TABLE_NAME  DEPENDENCIES
----------- ------------
TABLA1      DISABLED
TABLA2      ENABLED

17:36:20 SQL> insert into TABLA2 (numero, letra) values (7,'o');
17:36:20 SQL> insert into TABLA2 (numero, letra) values (8,'p');
17:36:20 SQL> insert into TABLA2 (numero, letra) values (9,'q');
17:36:20 SQL> insert into TABLA2 (numero, letra) values (10,'r');
17:36:20 SQL> insert into TABLA2 (numero, letra) values (11,'s');
17:36:20 SQL> insert into TABLA2 (numero, letra) values (12,'t');
1 fila creada. 
1 fila creada.
1 fila creada.
1 fila creada.
1 fila creada.
1 fila creada.
17:36:39 SQL> commit;
Confirmacion terminada.

Consultamos el ORA_ROWSCN de la TABLA2.

17:36:53 SQL> select to_char(ORA_ROWSCN),numero,letra  from TABLA2;

TO_CHAR(ORA_ROWSCN)                          NUMERO LETRA
---------------------------------------- ---------- -----
13506913556387                                    7 o
13506913556387                                    8 p
13506913556387                                    9 q
13506913556387                                   10 r
13506913556387                                   11 s
13506913556387                                   12 t

17:37:05 SQL> select scn_to_timestamp(to_char(t.ORA_ROWSCN)) as timestamp, t.numero, t.letra from dual, TABLA2 t;

TIMESTAMP                         NUMERO LETRA
----------------------------- ---------- -----
06/09/24 17:36:40,000000000            7 o
06/09/24 17:36:40,000000000            8 p
06/09/24 17:36:40,000000000            9 q
06/09/24 17:36:40,000000000           10 r
06/09/24 17:36:40,000000000           11 s
06/09/24 17:36:40,000000000           12 t

¿Qué ocurre si pasado un tiempo, insertamos un nuevo registro en cada tabla?

18:14:49 SQL> insert into TABLA1 (numero, letra) values (7,'g');
1 fila creada.
18:15:10 SQL> commit;
Confirmacion terminada.

18:15:14 SQL> insert into TABLA2 (numero, letra) values (13,'v');
1 fila creada.
18:15:35 SQL> commit;
Confirmacion terminada.

Como verás a continuación, en la TABLA1 donde ROWDEPENDENCIES no está activado, se han actualizado los valores de todos los registros a la última modificación.
En TABLA2, donde teníamos activado el seguimiento fila a fila, solo ha actualizado el SCN de esa fila, y tenemos la fecha y hora exacta de inserción de esa fila concreta.

18:15:37 SQL> select scn_to_timestamp(to_char(t.ORA_ROWSCN)) as timestamp, t.numero, t.letra from dual, TABLA1 t;

TIMESTAMP                         NUMERO LETRA
----------------------------- ---------- -----
06/09/24 18:15:07,000000000            1 a
06/09/24 18:15:07,000000000            2 b
06/09/24 18:15:07,000000000            3 c
06/09/24 18:15:07,000000000            4 d
06/09/24 18:15:07,000000000            5 e
06/09/24 18:15:07,000000000            6 f
06/09/24 18:15:07,000000000            7 g

7 filas seleccionadas.

Transcurrido: 00:00:00.01
18:15:45 SQL> select scn_to_timestamp(to_char(t.ORA_ROWSCN)) as timestamp, t.numero, t.letra from dual, TABLA2 t;

TIMESTAMP                         NUMERO LETRA
----------------------------- ---------- -----
06/09/24 17:36:40,000000000            7 o
06/09/24 17:36:40,000000000            8 p
06/09/24 17:36:40,000000000            9 q
06/09/24 17:36:40,000000000           10 r
06/09/24 17:36:40,000000000           11 s
06/09/24 17:36:40,000000000           12 t
06/09/24 18:15:34,000000000           13 v

Para finalizar, vamos a mirar el tamaño de las tablas, para ver si es cierto que cada fila aumenta en 6 bytes si tenemos activado ROWDEPENDENCIES.

select segment_name, sum(bytes) table_size_Bytes
from dba_segments
where segment_name in ('TABLA1','TABLA2')
group by segment_name;

SEGMENT_NAME   TABLE_SIZE_BYTES
-------------- ----------------
TABLA1                    65536
TABLA2                    65536

Si alguien, al igual que yo, ha pensado que esto podía suceder porque sean tablas muy pequeñas…
He creado dos nuevas tablas, copia de otra que ya tenía con 1.238.762 registros. Una con y otra sin ROWDEPENDENCIES. El resultado es similar. Por lo tanto, no puedo confirmar que lo que indica en la documentación sobre el aumento de tamaño sea cierto.

select segment_name, sum(bytes) table_size_Bytesfrom dba_segmentswhere segment_name in ('TABLA3','TABLA4')group by segment_name;
SEGMENT_NAME   TABLE_SIZE_BYTES
-------------- ----------------
TABLA4                 60817408
TABLA3                 60817408

Espero que esta práctica os pueda ayudar en alguna ocasión, y que hayáis comprendido bien los conceptos.
Nos vemos en la siguiente 😉

Cele Cueva 10 de septiembre de 2024

6 bytes / activar / campo / conocer / crear / create / crecimiento / dependencia de fila / dependencies / desactivar / deshabilitar / disable / documentacion / ejemplos / enable / fecha / fila / habilitar / hora / modificacion / ora_rowscn / Oracle / practica / pseudocolumna / registro / row / rowdependencies / SCN / scn_to_timestamp / tabla / table / tamaño / teoria / to_char(ora_rowscn) / ultima /

ORA_ROWSCN ¿Cómo conocer la fecha y hora de última modificación de una fila dentro de una tabla?

El Pensamiento

La Teoría

Ejemplos

Me gusta esto:

Deja un comentarioCancelar respuesta

ORA_ROWSCN ¿Cómo conocer la fecha y hora de última modificación de una fila dentro de una tabla?

El Pensamiento

La Teoría

Ejemplos

Comparte este artículo en:

Me gusta esto:

Deja un comentarioCancelar respuesta