Consultando dados modificados nas últimas sessões do Oracle com FLASHBACK

O Oracle possui uma funcionalidade interessante chamada FLASHBACK, onde é possível consultar versões anteriores de dados do banco de dados. Dependendo de como o banco foi configurado, é possível consultar dados de minutos ou até algumas horas atrás. Em ambientes de produção, provavelmente esta funcionalidade estará desabilitada.

Flashback query - permite consultar dados que existiam na base em determinado momento do passado. A consulta produzirá um resultado como se a query tivesse sido feita naquele momento do tempo, independente se dados foram alterados e comitados.

Flashback version query - permite consultar dados de vários momentos do passado de uma úniva vez e comparar o resultado.

Flashback transaction query - permite consultar o dicionário de dados para obter uma variedade de metadados sobre o histórico de transações - inclusive sobre a transação que causou a mudança mais recente.

Segue abaixo exemplo de Flashback query:

1
2
3
4
5
6

SELECT
   *
FROM
   CLIENTES
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '0 0:01:30' DAY TO SECOND
;

ou:

1
2
3
4
5
6

SELECT
   *
FROM
   CLIENTES
AS OF SCN scn_expression
;

SCN significa System Change Number. Cada linha de uma tabela do Oracle possui uma pseudo-coluna com um SCN atribuído, cujo valor muda automaticamente a cada commit.

No primeiro exemplo, internamente o Oracle primeiro converte o timestamp para o SCN mais próximo disponível antes de buscar pelos dados históricos. Os valores de SCN possuem exatamente 3 segundos de granularidade e, sendo assim, não existe um mapeamento exato um-para-um de timestamp para SCN.

O período de retenção para dados histórico varia de ambiente para ambiente, dependendo da configuração do banco de dados. Na prática, a faixa de período de tempo em que é possível recuperar dados históricos usando Flashback é de alguns minutos à algumas horas, possivelmente um dia ou mais, mas geralmente não mais do que isso.

Segue abaixo exemplo de Flashback Version Query:

1
2
3
4
5
6
7
8

SELECT
   *
FROM
   CLIENTES
VERSIONS BETWEEN TIMESTAMP 
   timestamp_expression_1 
   AND timestamp_expression_2
;

ou:

1
2
3
4
5
6
7
8

SELECT
   *
FROM
   CLIENTES
VERSIONS BETWEEN SCN
   scn_expression_1
   AND scn_expression_2
;

Segue abaixo pseudo-colunas que podem ser usadas com Flashback Query Version:

VERSIONS_STARTTIME ou VERSIONS_STARTSCN - Tempo ou SCN de quando a versão da linha foi criada. Se NULA, então a linha foi criada antes do tempo inicial especificado pela cláusula BETWEEN.

VERSIONS_ENDTIME ou VERSIONS_ENDSCN - Tempo ou SCN de até quando versão da linha durou. Se NULA, então a linha ainda existe ou a versão da linha é resultado de um DELETE (ver VERSIONS_OPERATION).

VERSIONS_XID - Identifica a transação que criou a linha

VERSIONS_OPERATION - Identifica a operação que causou a apariração da versão da linha no histórico: 'I' para INSERT, 'U' para UPDATE e 'D' para DELETE.

Note que Flashback Query Version tem a ver com linha. Portanto, as pseudo-colunas acima se aplicam a cada linha do resultado.

Tanto a sintaxe com timestamp quanto a sintaxe com SCN podem utilizar as palavras reservadas MINVALUE e MAXVALUE, o que significa que o valor de timestamp ou scn mais antigo e o valor de timestamp ou scn mais recentes serão considerados:

1
2
3
4
5
6
7
8

SELECT
   *
FROM
   CLIENTES
VERSIONS BETWEEN SCN
   MINVALUE
   AND MAXVALUE;
 

O Flashback Transaction Query é uma funcionalidade do dicionário de dados que permite consultar o histórico de transações. A view é a FLASHBACK_TRANSACTION_QUERY:

Veja que o campo XID possui a mesma informação que a pseudo-coluna VERSIONS_XID do Flashback Query Version, portanto, as duas informações podem ser cruzadas.

Este cruzamento, juntamente com o campo UNDO_SQL pode ser usado para obter o código SQL que irá reverter os dados da tabela para o estado anterior da transação:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT 
   UNDO_SQL 
FROM   
   FLASHBACK_TRANSACTION_QUERY 
WHERE  
   XID = (
      SELECT   
         VERSIONS_XID
      FROM
         CLIENTES
      VERSIONS BETWEEN TIMESTAMP 
         MINVALUE
         AND MAXVALUE
      WHERE
         CLIENTE_ID = 1
         AND VERSIONS_OPERATION = 'D'
   )
;

O código SQL acima irá trazer todos os comandos INSERT que irão reinserir os registros que foram deletados da tabela de clientes pela transação representada por VERSIONS_XID (ou XID). Vale lembrar que o valor dos dois campos identificam uma transação do Oracle, valor este que é criado pelo Oracle a cada nova transação. O valor é armazenado em formato RAW pelo Oracle e não existe maneira de mapeá-lo para um formato entendível para seres humanos utilizando uma função do Oracle. A função RAWTOHEX pode ser utilizada para converter o valor para hexadecimal, porém, o valor convertido não fará sentido.