Seguimiento de Cambios en los Datos

Objetivos:
•Hacer un seguimiento de los cambios en datos durante un período de tiempo.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________

Seguimiento de Cambios en los Datos.

Puede suceder que, de algún modo, haya datos en la tabla que se hayan cambiado incorrectamente. Para investigar esto, puede utilizar varias consultas de flashback para ver los datos de filas en puntos concretos en el tiempo. Lo que es más eficaz, puede utilizar la función Consulta de Versiones de Flashback para ver todos los cambios efectuados en una fila durante un período de tiempo. Esta función le permite agregar una cláusula VERSIONS a una sentencia SELECT que especifique un SCN(System Change Number) o rango de registro de hora en el que desee ver cambios en los valores de fila. La consulta también puede devolver metadatos asociados como, por ejemplo, la transacción responsable del cambio. 

Es más, después de identificar una transacción errónea, puede utilizar la función Consulta de Versiones de Flashback para identificar otros cambios que haya realizado la transacción. Puede utilizar entonces la función Consulta de Versiones de Flashback para restaurar la tabla a un estado anterior a la realización de los cambios. 

Puede utilizar una consulta en una tabla con una cláusula VERSIONS para producir todas las versiones de todas las filas que existen o que hayan existido entre el momento en que se emitió la consulta y los segundos undo_retention anteriores al momento actual. undo_retention es un parámetro de inicialización de ajuste automático. Una consulta que incluye una cláusula VERSIONS se conoce como consulta de versiones. Los resultados de una consulta de versiones se comportan como si se hubiera aplicado la cláusula WHERE a las versiones de las filas. La consulta de versiones devuelve versiones de las filas sólo en transacciones. 

SCN(Número de Cambio del Sistema): Oracle Server asigna un SCN para identificar los registros de rehacer para cada transacción validada.

La cláusula VERSIONS no cambia el plan de la consulta. Por ejemplo, si ejecuta una consulta en una tabla que utilice el método de acceso a índices, la misma consulta de la misma tabla con una cláusula VERSIONS continúa utilizando el método de acceso a índices. Las versiones de las filas devueltas por la consulta de versiones son versiones de las filas en las transacciones. La cláusula VERSIONS no afecta al comportamiento transaccional de una consulta. Esto significa que una consulta en una tabla con una cláusula VERSIONS sigue heredando el entorno de la consulta de la transacción en curso. 

La cláusula VERSIONS por defecto se puede especificar como VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE AND MAXVALUE. 

La cláusula VERSIONS es una extensión SQL únicamente para consultas. Puede tener operaciones DML y DDL que utilicen una cláusula VERSIONS dentro de las subconsultas. La consulta de versiones recupera todas las versiones validadas de las filas seleccionadas. Los cambios realizados por la transacción activa actual no se devuelven. La consulta de versiones recupera todas las encarnaciones de las filas. Esto significa en esencia que las versiones devueltas incluyen las versiones suprimidas y subsiguientes reinsertadas de las filas.

El acceso a filas para una consulta de versiones se puede definir en una de las siguientes dos categorías: 

•Acceso a filas basado en ROWID: En el caso del acceso basado en ROWID, se devuelven todas las versiones de los ROWID independientemente del contenido de las filas. Básicamente, esto significa que se devuelven todas las versiones de la ranura del bloque indicado por el ROWID. 

•Acceso a todas las demás filas: Para el acceso a todas las demás filas, se devuelven todas las versiones de las filas.

Cláusula VERSIONS BETWEEN.

Puede utilizar la cláusula VERSIONS BETWEEN para recuperar todas las versiones de las filas que existen o que han existido entre el momento en que se emitió la consulta y un punto pasado en el tiempo. 

Si el momento de retención de deshacer es menor que el momento de límite inferior/SCN de la cláusula BETWEEN, la consulta recupera únicamente las versiones hasta el momento de retención de deshacer. El intervalo de tiempo de la cláusula BETWEEN se puede especificar como intervalo SCN o como intervalo de reloj. Este intervalo de tiempo se cierra en los límites inferior y superior.

Vista FLASHBACK_TRANSACTION_QUERY.

La vista FLASHBACK_TRANSACTION_QUERY provee informaciones acerca de las versiones o estados de la data. Estas informaciones incluyen el SQL necesario para revertir cada cambio. Para poder consultar dicha vista es necesario tener el privilegio: SELECT ANY TRANSACTION. Las consultas hechas a esta vista se conocen como: consultas de transacción flashback (flashback transaction queries).

Columna	Tipo Dato	Descripción
XID	RAW(8)	Identificador de Transacción.
START_SCN	NUMBER	Fecha Inicio de transacción en SCN
START_TIMESTAMP	DATE	Fecha Inicio de transacción en Timestamp
COMMIT_SCN	NUMBER	Fecha cuando se completó la transacción en SCN(NULL para las transacciones Activas.)
COMMIT_TIMESTAMP	DATE	Fecha cuando se completó la transacción en Timestamp (NULL para las transacciones Activas.)
LOGON_USER	VARCHAR2(30)	Usuario que ejecutó la transacción.
UNDO_CHANGE#	NUMBER	Numero de cambio UNDO (1 o mayor)
OPERATION	VARCHAR2(32)	Operación DML ejecutada en la transacción: D - Delete  I - Insert  U - Update  B  UNKNOWN
TABLE_NAME	VARCHAR2(256)	Tabla en la cual se aplicó la sentencia DML.
TABLE_OWNER	VARCHAR2(32)	Dueño de la tabla en la cual se aplicó la sentencia DML.
ROW_ID	VARCHAR2(19)	Identificador del registro en el cual se aplicó la sentencia DML.
UNDO_SQL	VARCHAR2(4000)	SQL para deshacer/descartar el DML especificado en OPERATION

Parámetro UNDO_RETENTION.

El parámetro UNDO_RETENTION contiene el tiempo limite(en segundos) de duración de una retención UNDO. Oracle Server automáticamente administra el valor de este parámetro para así satisfacer los requerimientos UNDO de los queries.

Es necesario que el tablespace asignado a las retenciones UNDO tenga suficiente espacio para satisfacer el valor de este parámetro. Si una transacción activa requiere espacio UNDO y el tablaspace no tiene disponible, entonces el sistema reutiliza espacios de UNDO que aun no han expirado. Esta acción provoca excepciones como: "snapshot too old" al momento de ejecutar consultas.

Puede consultar la Vista de rendimiento dinámico V$UNDOSTAT (columna TUNED_UNDORETENTION) para determinar el valor de dicho parámetro asignado al actual tablespace.

Ejemplos:
CREATE TABLE datos_personas
(
  codigo NUMBER(6) CONSTRAINT pk_cod_per PRIMARY KEY,
  nombre VARCHAR2(46) CONSTRAINT nn_nombre_per NOT NULL,
  sexo CHAR(1) CONSTRAINT ck_sex_per CHECK(sexo IN ('F','M')),
  edad NUMBER(2) CONSTRAINT ck_edad_per CHECK(edad BETWEEN 18 AND 40),
  salario NUMBER(5)
);

/*Creamos la tabla datos_personas con algunos campos que usaremos para nuestros futuros ejemplos*/

---
INSERT INTO datos_personas
  SELECT 
        ROWNUM,
        first_name||' '||last_name,
        NULL,
        NULL,
        salary
  FROM employees
  WHERE ROWNUM < 9;
COMMIT;
/*Insertamos 8 registros cualquiera en nuestra tabla y completamos la transacción.*/
---
SELECT
      versions_starttime "Fecha Inicio",
      TIMESTAMP_TO_SCN(versions_starttime) "SCN F-I",
      versions_endtime   "Fecha Fin",
      DECODE (versions_endtime, NULL, NULL, TIMESTAMP_TO_SCN(versions_endtime)) "SCN F-F",
      codigo,
      nombre,
      sexo,
      edad,
      salario
FROM datos_personas
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
/*Consultamos las versiones de los datos en la tabla: datos_personas.*/

/*La imagen muestra versiones de los datos en la tabla datos_personas; El campo "Fecha Inicio o versions_starttime " muestra la fecha cuando la transacción se inició; el campo "SCN F-I" tiene el mismo valor de versions_starttime  pero en formato SCN; Los campos Fecha Fin o versions_endtime" aparecen Nulos ya que esa es la version mas reciente de la data, por lo cual no ha sufrido cambios hasta ese momento. Nota: Uso DECODE para evitar un error(En caso de ser Nulo) al tratar de convertir el valor versions_endtime a SCN.*/

---
SELECT *
FROM flashback_transaction_query
WHERE xid IN 
          (
            SELECT versions_xid
            FROM datos_personas 
            VERSIONS BETWEEN SCN  3268292
            AND
            TIMESTAMP_TO_SCN('19-JUL-16 05.50.29.000000000 PM')
          );

/*Consultamos los cambios realizados en la tabla datos_personas, pero ahora a través de la Vista: flashback_transaction_query. Nota: Uso el SCN retornado en el primer ejemplo como rango inferior y una fecha mayor como rango superior, si ejecutas el mismo Query debes cambiar los rangos de acuerdo a tu necesidad(para evitar error usar: MINVALUE AND MAXVALUE).*/

ALTER SYSTEM SET UNDO_RETENTION = 2400;

/*Alteramos el parámetro UNDO_RETENTION para así incrementar el tiempo de duración UNDO para nuestra session a 40 Minutos(2400 segundos).*/

---
UPDATE datos_personas
SET salario = 50000
WHERE codigo IN (1,2);
COMMIT;
/*Actualizamos el Salario de dos personas y guardamos.*/
---
SELECT
      versions_starttime "Fecha Inicio",
      TIMESTAMP_TO_SCN(versions_starttime) "SCN F-I",
      versions_endtime   "Fecha Fin",
      DECODE (versions_endtime, NULL, NULL, TIMESTAMP_TO_SCN(versions_endtime)) "SCN F-F",
      codigo,
      nombre,
      sexo,
      edad,
      salario
FROM datos_personas
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

/*Consultamos las versiones de los datos nuevamente, para notar los cambios realizados.*/

/*La imagen muestra como identificar las versiones mas recientes de la data; Notar que los registros con valores no Nulos en los campos Fecha Fin y SCN F-F(encerrados en Rojo) son equivalentes a los valores de Fecha Inicio y SCN F-I de las versiones mas recientes.*/

---
SELECT *
FROM flashback_transaction_query
WHERE xid IN 
          (
            SELECT versions_xid
            FROM datos_personas 
            VERSIONS BETWEEN SCN  3268292
            AND
            MAXVALUE
          );

/*Consultamos una vez mas la Vista: flashback_transaction_query; los registros encerrados en verde equivalen a nuestra ultima actualización en la tabla datos_personas .*/

UPDATE datos_personas
SET sexo = 'F'
WHERE codigo IN (2,5);
COMMIT;
/*Cambiamos el sexo de algunos registros.*/
---
SELECT
      versions_starttime "Fecha Inicio",
      TIMESTAMP_TO_SCN(versions_starttime) "SCN F-I",
      versions_endtime   "Fecha Fin",
      DECODE (versions_endtime, NULL, NULL, TIMESTAMP_TO_SCN(versions_endtime)) "SCN F-F",
      codigo,
      nombre,
      sexo,
      edad,
      salario
FROM datos_personas
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

/*Una vez mas mostramos las versiones de los datos; Los registros encerrados en color verde limón para el registro código 5 es la vieja version; El color rojo intenso para el registro código 2 equivalen a la version vieja.*/

---
SELECT *
FROM flashback_transaction_query
WHERE xid IN 
          (
            SELECT versions_xid
            FROM datos_personas 
            VERSIONS BETWEEN SCN  3268292
            AND
            TIMESTAMP_TO_SCN('19-JUL-16 05.56.00.000000000 PM')
          );

/*Los registros en Verde equivalen a los últimos cambios.*/
---
UPDATE datos_personas
SET sexo = 'M'
WHERE sexo IS NULL;
COMMIT;
/*Ahora asignamos el valor 'M' en sexo para los registros con ese campo NULL.*/
---
SELECT
      versions_starttime "Fecha Inicio",
      TIMESTAMP_TO_SCN(versions_starttime) "SCN F-I",
      versions_endtime   "Fecha Fin",
      DECODE (versions_endtime, NULL, NULL, TIMESTAMP_TO_SCN(versions_endtime)) "SCN F-F",
      codigo,
      nombre,
      sexo,
      edad,
      salario
FROM datos_personas
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

/*Registros en Rojo son la Vieja version; Los en que están en verde son la mas reciente.*/

_____________________________________________________________________________________

Fuentes: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1, docs.oracle.com