Oracle Flashback Table

De Dos Ideas.
Revisión del 15:09 25 jul 2008 de 201.251.185.130 (discusión) (→‎Ver también)
Saltar a: navegación, buscar

La alternativa Flashback Table, es introducida en [Oracle10g] y retorna los datos de una tabla al estado en que estaba en un tiempo pasado descripto por un SCN o un timestamp . Esta alternativa también utiliza AUM (Automatic Undo Management) para realizar la vuelta atrás. Por lo tanto las limitaciones del tiempo al que puede volverse atrás cumple con las mismas limitaciones que para Flashback Query.

La operación de restore implícita en el FLASHBACK TABLE, se realiza en forma online, o sea que en ningún momento hay que poner offline alguna parte de la base. Lo que si hay que tener en cuenta, que esta sentencia adquiere un lock DML exclusivo sobre la tabla en que se está haciendo el FLASHBACK TABLE.

El Flashback Table mantinene automáticamente todos los atributos de la tabla, como por ejemplo los índices, triggers y constraints. Mantiene la integridad de los datos como están especificadas por las constraints, o sea que las tablas vuelven atrás siempre y cuando no viole ninguna de las constraints actuales. Esto incluye las constraints de integridad referencial (FK) especificadas entre la tabla incluída en el FLASHBACK y otra tabla que no esta incluída en el FLASHBACK.

Para poder realizar un flashback table el usuario y la tabla deben tener los siguientes privilegios: 

ALTER TABLE <nombre_tabla> ENABLE ROW MOVEMENT;
GRANT SELECT, INSERT, DELETE, UPDATE ON <nombre_tabla> TO <usuario>;
GRANT FLASHBACK ON <nombre_tabla> TO <usuario>;

El ultimo privilegio no es necesario si el usuario ya tiene el siguiente privilegio: 

GRANT FLASHBACK ANY TABLE TO <usuario>;

Aun luego de la operación de FLASHBACK, los datos originales no se pierden. Uno puede revertir los datos al estado original.

Preparación de Ambiente

Para realizar las pruebas vamos a crear dos tablas relacionadas a través de una FK, y una de las tablas tendrá un trigger.

De esta manera podremos observar que pasa cuando se vuelve para atrás el estado de la tabla con las constraints y la ejecución de los triggers.

Para observar el comportamiento del trigger creamos una tabla de log donde el trigger irá logueando su actividad.

Privilegios

Para poder consultar el SCN actual: 

system@LAB10G> grant select on v_$database to scott;
Concesion terminada correctamente.

sys@LAB10G> grant flashback any table to scott;
Concesion terminada correctamente.

Creación de Tablas de Prueba

sys@LAB10G> connect scott/tiger
Conectado.

scott@LAB10G> CREATE TABLE dept_flash
 2  ( DEPTNO  NUMBER(2),
 3    DNAME   VARCHAR2(14),
 4    LOC     VARCHAR2(13)
 5  );
Tabla creada.

scott@LAB10G> ALTER TABLE dept_flash ADD CONSTRAINT pk_dept_flash PRIMARY KEY (DEPTNO);
Tabla modificada.

scott@LAB10G> INSERT INTO dept_flash (select * from dept);
4 filas creadas.

scott@LAB10G> COMMIT;
Confirmacion terminada.

scott@LAB10G> CREATE TABLE emp_flash
 2  ( EMPNO     NUMBER(4),
 3    ENAME     VARCHAR2(10),
 4    JOB       VARCHAR2(9),
 5    MGR       NUMBER(4),
 6    HIREDATE  DATE,
 7    SAL       NUMBER(7,2),
 8    COMM      NUMBER(7,2),
 9    DEPTNO    NUMBER(2)
10  );
Tabla creada.

scott@LAB10G> ALTER TABLE emp_flash ADD CONSTRAINT emp_flash_pk PRIMARY KEY (EMPNO);
Tabla modificada.

scott@LAB10G> ALTER TABLE emp_flash ADD CONSTRAINT emp_flash_fk
 2  FOREIGN KEY (DEPTNO) REFERENCES dept_flash (DEPTNO);
Tabla modificada.

scott@LAB10G> INSERT INTO emp_flash (select * from emp);
14 filas creadas.

scott@LAB10G> COMMIT;
Confirmacion terminada.

scott@LAB10G> create table emp_flash_log
 2  (fecha varchar2(20),
 3  operacion varchar2(50));
Tabla creada.

scott@LAB10G> CREATE OR REPLACE TRIGGER emp_flash_trg
 2    BEFORE INSERT OR UPDATE OR DELETE ON emp_flash FOR EACH ROW
 3  BEGIN
 4     if INSERTING then
 5        insert into emp_flash_log
 6        values
 7        (to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'),
 8         'Operacion Insert '||to_char(:NEW.empno));
 9     end if;
10     if DELETING then
11        insert into emp_flash_log
12        values
13        (to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'),
14         'Operacion Delete '||to_char(:OLD.empno));
15     end if;
16     if UPDATING then
17        insert into emp_flash_log
18        values
19        (to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'),
20        'Operacion Update '||to_char(:OLD.empno)||' '||to_char(:NEW.empno));
21     end if;
22  END emp_flash_trg;
23  /
Disparador creado.

Datos

scott@LAB10G> select * from dept_flash;
 DEPTNO DNAME          LOC
------- -------------- -------------
     10 ACCOUNTING     NEW YORK
     20 RESEARCH       DALLAS
     30 SALES          CHICAGO
     40 OPERATIONS     BOSTON

scott@LAB10G> select * from emp_flash

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM  DEPTNO
------ ---------- --------- ----- --------- ----- ----- -------
  7369 SMITH      CLERK      7902 17-DIC-80   800            20
  7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300      30
  7521 WARD       SALESMAN   7698 22-FEB-81  1250   500      30
  7566 JONES      MANAGER    7839 02-ABR-81  2975            20
  7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400      30
  7698 BLAKE      MANAGER    7839 01-MAY-81  2850            30
  7782 CLARK      MANAGER    7839 09-JUN-81  2450            10
  7788 SCOTT      ANALYST    7566 19-ABR-87  3000            20
  7839 KING       PRESIDENT       17-NOV-81  5000            10
  7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0      30
  7876 ADAMS      CLERK      7788 23-MAY-87  1100            20
  7900 JAMES      CLERK      7698 03-DIC-81   950            30
  7902 FORD       ANALYST    7566 03-DIC-81  3000            20
  7934 MILLER     CLERK      7782 23-ENE-82  1300            10


Ejemplo

Realizo Flashback table de EMP_FLASH. Comportamiento trigger I

scott@LAB10G> select current_scn from v$database;
CURRENT_SCN
-----------
    1223943

scott@LAB10G> insert into emp_flash values
 2  (8888,'Gonzalez','Contador', 7782, sysdate, 1000, null, 10);
1 fila creada.

scott@LAB10G> commit;
Confirmacion terminada.

scott@LAB10G> select * from emp_flash_log;
FECHA                OPERACION
-------------------- ---------------------
29/08/2007 16:33:07  Operacion Insert 8888

scott@LAB10G> flashback table emp_flash to scn 1223943;
flashback table emp_flash to scn 1223943
                *
ERROR en linea 1:
ORA-08189: no se puede realizar flashback en la tabla
porque el movimiento de filas no esta activado

scott@LAB10G> alter table emp_flash enable row movement;
Tabla modificada.

scott@LAB10G> flashback table emp_flash to scn 1223943;
Flashback terminado.

scott@LAB10G> select * from emp_flash_log;
FECHA                OPERACION
-------------------- ---------------------
29/08/2007 16:33:07  Operacion Insert 8888

scott@LAB10G> select * from emp_flash where empno=8888;
ninguna fila seleccionada

Antes que nada con este ejemplo mostramos que si la tabla no tiene habilitado el ROW MOVEMENT, la sentencia FLASHBACK TABLE no funciona.

Con respecto al comportamiento del trigger de la tabla sobre la que estamos haciendo el FLASHBACK TABLE podemos decir lo siguiente:

Cuando ejecuto un INSERT sobre la tabla, se ejecuta el trigger, pero cuando hago la vuelta atrás con el FLASHBACK, el trigger no realiza ninguna operación.

Este es el comportamiento default de la sentencia FLASHBACK TABLE. Antes de realizar la vuelta atrás se deshabilita el trigger, y una vez finalizada la vuelta atrás, lo vuelve a habilitar.


Realizo Flashback table de EMP_FLASH. Comportamiento trigger II

scott@LAB10G> select current_scn from v$database;
CURRENT_SCN
-----------
    1224004

scott@LAB10G> insert into emp_flash
 2    values (9999,'Perez','Abogado', 7782, sysdate, 1000, null, 10);
1 fila creada.

scott@LAB10G> commit;
Confirmacion terminada.

scott@LAB10G> select * from emp_flash_log;
FECHA                OPERACION
-------------------- ---------------------
29/08/2007 16:33:07  Operacion Insert 8888
29/08/2007 16:35:25  Operacion Insert 9999

scott@LAB10G> flashback table emp_flash to scn 1224004 enable triggers;
Flashback terminado.

scott@LAB10G> select * from emp_flash_log;
FECHA                OPERACION
-------------------- ---------------------
29/08/2007 16:33:07  Operacion Insert 8888
29/08/2007 16:35:25  Operacion Insert 9999
29/08/2007 16:36:16  Operacion Insert 7369
29/08/2007 16:36:16  Operacion Insert 7499
29/08/2007 16:36:16  Operacion Insert 7521
29/08/2007 16:36:16  Operacion Insert 7566
29/08/2007 16:36:16  Operacion Insert 7654
29/08/2007 16:36:16  Operacion Insert 7698
29/08/2007 16:36:16  Operacion Insert 7782
29/08/2007 16:36:16  Operacion Insert 7788
29/08/2007 16:36:16  Operacion Insert 7839
29/08/2007 16:36:16  Operacion Insert 7844
29/08/2007 16:36:16  Operacion Insert 7876
29/08/2007 16:36:16  Operacion Insert 7900
29/08/2007 16:36:16  Operacion Insert 7902
29/08/2007 16:36:16  Operacion Insert 7934
16 filas seleccionadas.

scott@LAB10G> select * from emp_flash where empno in (8888,9999);
ninguna fila seleccionada

Para romper el comportamiento default existe la opción ENABLE TRIGGERS de lasentencia FLASHBACK TABLE: 

FLASHBACK TABLE <tabla> TO SCN nnnnnnn ENABLE TRIGGERS;

Lo que me resulta extraño de esta opción es que parecería que el FLASHBACK TABLE trunca la tabla y luego hace INSERT de cada una de las filas existentes antes del SCN informado. De esta manera no se como puede hacerlo utilizando solo la información de UNDO. Tema a seguir investigando, pero es mas sobre el funcionamiento interno de Oracle.


Realizo Flashback table de DEPT_FLASH. Comportamiento constraints.

scott@LAB10G> select current_scn from v$database;
CURRENT_SCN
-----------
    1224882

scott@LAB10G> insert into dept_flash values  (60, 'Sistemas','Buenos Aires')
1 fila creada.

scott@LAB10G> commit;
Confirmacion terminada.

scott@LAB10G> insert into emp_flash values (7777,'Garcia','Sistemas', 7782, sysdate, 1000,null, 60);
1 fila creada.

scott@LAB10G> commit;
Confirmacion terminada.

scott@LAB10G> flashback table dept_flash to scn 1224882;
flashback table dept_flash to scn 1224882
                *
ERROR en linea 1:
ORA-08189: no se puede realizar flashback en la tabla porque el movimiento de filas no esta activado

scott@LAB10G> alter table dept_flash enable row movement;
Tabla modificada.

scott@LAB10G> flashback table dept_flash to scn 1224882;
flashback table dept_flash to scn 1224882
*
ERROR en linea 1:
ORA-02091: transaccion con rollback
ORA-02292: restriccion de integridad (SCOTT.EMP_FLASH_FK) violada - registro secundario encontrado

Con este ejemplo queda claro que la vuelta atrás con FLASHBACK TABLE mantiene la integridad, no permitiendo eliminar o cambiar filas que tienen “hijos” asociados a su valor actual.


Rollback de la sentencia Flashback table.

Para poder realizar el “rollback” de la sentencia Flashback, se utilize un flashback al momento inmediato anterior a realizar la sentencia.

Por este motivo siempre es conveniente consultar el SCN antes de realizar el FLASHBACK TABLE. 

scott@LAB10G> select current_scn from v$database;
CURRENT_SCN
-----------
    1252763

scott@LAB10G> insert into emp_flash
 2  values
 3  (8888, 'Lopez', 'Vendedor', 7698, sysdate, 1000, null, 30);
1 fila creada.

scott@LAB10G> commit;
Confirmacion terminada.

scott@LAB10G> select current_scn from v$database;
CURRENT_SCN
-----------
    1252784

scott@LAB10G> flashback table emp_flash to scn 1252763;
Flashback terminado.

scott@LAB10G> select * from emp_flash where empno=8888;
ninguna fila seleccionada

scott@LAB10G> flashback table emp_flash to scn  1252784;
Flashback terminado.

scott@LAB10G> select * from emp_flash where empno=8888;
 EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM  DEPTNO
------ ---------- --------- ----- -------- ----- ----- -------
  8888 Lopez      Vendedor   7698 30/08/07  1000            30

Ver también

Obtenido de «https://dosideas.com./wiki/index.php?title=Oracle_Flashback_Table&oldid=289»