Um pouco do Flashback Versions Query e do Flashback Transaction Query disponíveis a partir do Oracle 10g ...

Olá,

A funcionalidade do recurso Flashback Query disponível desde a versão Oracle 9i e abordada também nos artigos de Maio/2007 e Maio/2008, foi aperfeiçoada no Oracle 10g para incluir dois novos tipos de consultas: Flashback Versions Query e Flashback Transaction Query. O Flashback Versions Query fornece uma maneira simples e fácil de mostrar todas as versões de todas as linhas em uma tabela entre dois SCN's ou tempos de intervalo (time stamps), informando se as linhas foram inseridas, deletadas ou atualizadas. Na verdade, acredito que o Flashback Versions Query é uma extensão à linguagem SQL que permite ao DBA recuperar diferentes versões de linhas de tabela em qualquer intervalo de tempo. Uma nova versão de registro será criada toda vez que o comando COMMIT for emitido, e mesmo que uma linha seja deletada e re-inserida várias vezes, todas estas alterações estarão disponíveis para acesso. É importante lembrar que o parâmetro UNDO_RETENTION controla e especifica quanto tempo os blocos de dados Oracle alterados ficarão disponíveis no segmento de UNDO até serem sobre-gravados, portanto é bom certificar que não só este parâmetro esteja corretamente setado, mas também que o tablespace de UNDO seja grande suficiente para que as alterações realizadas no banco de dados estejam disponíveis para uso do Flashback Query quando necessário. No mais, para invocar esta funcionalidade é necessário utilizar a cláusula VERSIONS BETWEEN na sentença SELECT.

A sintaxe para uso do Flashback Versions Query é a seguinte:

 SELECT [pseudo_columns]...FROM table_name
   VERSIONS BETWEEN
  {SCN | TIMESTAMP {expr | MINVALUE} AND
  {expr | MAXVALUE}}
  [AS OF {SCN|TIMESTAMP expr}]
 WHERE [pseudo_column | column] . . .

Já o Flashback Transaction Query usa a view de dicionário de dados FLASHBACK_TRANSACTION_QUERY para recuperar informações de transações de banco de dados para todas as tabelas envolvidas em uma transação. Esta view possui uma coluna UNDO_SQL que fornece a sentença SQL que poderá ser utilizada para desfazer uma mudança feita anteriormente. A propósito, nas versões anteriores ao Oracle 10g, o LogMiner poderia ser utilizado para fornecer estas informações. É importante salientar que para ter acesso a esta view, o usuário de banco de dados deverá ter o privilégio de sistema SELECT ANY TRANSACTION. Em resumo, caso seja identificado que um dado em uma linha de tabela foi modificado erroneamente, poderemos então usar o Flashback Transaction Query para identificar o histórico das sentenças de undo SQL e utilizá-las para reverter estas modificações.

A view FLASHBACK_TRANSACTION_QUERY contém as seguintes colunas:

Nome             Descrição
---------------- ---------------------------------------------------------------------------
XID              Identificador da transação.
START_SCN        Número do SCN que inicia a transação.
START_TIMESTAMP  Horário de início da transação.
COMMIT_SCN       SCN gerado durante o COMMIT da transação. Será nulo para transações ativas.
COMMIT_TIMESTAMP Horário do COMMIT. Será nulo para transações ativas.
LOGON_USER       Usuário de banco de dados logado que realizou a transação.
UNDO_CHANGE#     Identificador de UNDO.
OPERATION        Operações realizadas pela transação (insert, delete, update).
TABLE_NAME       Nome da tabela onde as operações DML foram aplicadas.
TABLE_OWNER      Nome do usuário de banco de dados proprietário da tabela.
ROW_ID           O ROWID que foi modificado pela operação DML.
UNDO_SQL         SQL que poderá ser usado para desfazer a operação DML realizada.

Vale a pena salientar que a coluna LOGON_USER mostrada acima, poder ser útil para filtrar as transações realizadas por um usuário específico que possui privilégios de acesso às tabelas de propriedade de outro usuário. Vamos então a um pequeno exemplo prático onde usarei a procedure DBMS_LOCK.SLEEP apenas para gerar um intervalo de tempo (60 segundos) entre as transações:

-- Criando um usuário para teste
SYS> create user scott identified by tiger
 2  default tablespace users
 3  quota unlimited on users;

Usuário criado.

SYS> grant connect,resource to scott;

Concessão bem-sucedida.

SYS> grant select any transaction to scott;

Concessão bem-sucedida.

SYS> grant execute on dbms_lock to scott;

Concessão bem-sucedida.

SYS> connect scott/tiger
Conectado.

-- Criando de uma tabela para teste
SCOTT> create table t1 (id number);

Tabela criada.

-- Simulando operações DML's na tabela T1
SCOTT> set time on
09:12:34 SCOTT> insert into t1 values (1);

1 linha criada.

09:12:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:13:34 SCOTT> commit;

Validação completa.

09:13:34 SCOTT> insert into t1 values (2);

1 linha criada.

09:13:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:14:34 SCOTT> commit;

Validação completa.

09:14:34 SCOTT> insert into t1 values (3);

1 linha criada.

09:14:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:15:34 SCOTT> commit;

Validação completa.

09:15:34 SCOTT> update t1 set id = id*10;

3 linhas atualizadas.

09:15:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:16:34 SCOTT> commit;

Validação completa.

09:16:34 SCOTT> delete from t1;

3 linhas deletadas.

09:16:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:17:34 SCOTT> commit;

Validação completa.

09:17:36 SCOTT> set time off

-- Obtendo informações das versões das linhas
SCOTT> select versions_starttime stime,
   2         versions_endtime endtime,
   3         versions_xid xid,
   4         case
   5           when versions_operation = 'I' then 'INSERT'
   6           when versions_operation = 'U' then 'UPDATE'
   7           when versions_operation = 'D' then 'DELETE'
   8         end as operation,
   9         id
  10  from t1 versions between timestamp minvalue and maxvalue
  11  order by stime;

STIME             ENDTIME            XID              OPERAT         ID
----------------- ------------------ ---------------- ------ ----------
28/07/08 09:13:32 28/07/08 09:16:37  06001F00E9000000 INSERT          1
28/07/08 09:14:32 28/07/08 09:16:37  01000500F3000000 INSERT          2
28/07/08 09:15:32 28/07/08 09:16:37  02001000EF000000 INSERT          3
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         10
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         20
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         30
28/07/08 09:17:32                    04001D00F4000000 DELETE         10
28/07/08 09:17:32                    04001D00F4000000 DELETE         20
28/07/08 09:17:32                    04001D00F4000000 DELETE         30

9 linhas selecionadas.

De acordo com o resultado acima, podemos perceber, por exemplo, que o valor 1 contido na coluna ID da tabela T1, foi inserido pela transação 06001F00E9000000 às 09:13:32 e permaneceu com este valor até às 09:16:37 quando teve seu valor alterado para 10 até ser deletada por volta das 09:17:32. Podemos perceber também que as operações DELETE e UPDATE foram realizadas em uma mesma transação, ou seja, a transação 03001100F1000000 para UPDATE e a 04001D00F4000000 para DELETE.

Agora, vamos ver abaixo o que podemos obter selecionando dados a partir da view FLASHBACK_TRANSACTION_QUERY:

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('06001F00E9000000');

UNDO_SQL
-------------------------------------------------------------------------
delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';

O resultado acima mostra o comando DML necessário que deverá ser utilizado para desfazer a alteração realizada na transação identificada pelo id 06001F00E9000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('03001100F1000000');

UNDO_SQL
-------------------------------------------------------------------------
update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 03001100F1000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('04001D00F4000000');

UNDO_SQL
-------------------------------------------------------------------------
insert into "SCOTT"."T1"("ID") values ('10');
insert into "SCOTT"."T1"("ID") values ('20');
insert into "SCOTT"."T1"("ID") values ('30');

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 04001D00F4000000.

Por fim, irei obter abaixo, o histórico de todos os comandos DML's que poderão, de alguma forma, serem utilizados para desfazerem as alterações nos registros da tabela T1 desde a sua criação:

SCOTT> select to_char(commit_timestamp,'hh24:mi:ss') time,
   2         operation,
   3         undo_sql
   4  from flashback_transaction_query
   5  where table_name='T1' order by 1;

TIME     OPERATION UNDO_SQL
-------- --------- ----------------------------------------------------------------------
09:13:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';
09:14:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAB';
09:15:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAC';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('10');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('20');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('30');

9 linhas selecionadas.

Para maiores informações e exemplos de uso sobre ambas as tecnologias, você poderá acessar a documentação (em inglês) disponível no site da Oracle: Usando Flashback Version Query e Usando Flashback Transaction Query.