ORA_ROWSCN ¿Cómo conocer la fecha y hora de última modificación de una fila dentro de una tabla?
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 fComo 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 fCreamos 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 vPara 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 65536Si 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 60817408Espero que esta práctica os pueda ayudar en alguna ocasión, y que hayáis comprendido bien los conceptos.
Nos vemos en la siguiente 😉