Oracle Flashback Query

We review briefly in this post flashback query functionality provided by the Oracle DB Manager since version 9i.

Basically it is a type of sql that accesses data that existed in the database at an earlier time, but at the time running the sql may not exist or have been altered. To do this, Oracle uses the data that are available during a time segment UNDO. This segment, as is known, the data stored before a series of modifications. It is used to ensure consistency in the reading of a consultation prior to the confirmation of the changes (commit) and can be used in a possible recovery (rollback).

Graphic execution of Oracle Flashback Query

The ruling allows us to see flashback query data in the table that have been deleted or modified.Running a flashback query to access data from a picture of consistent data at a certain point, this time specifying to the system or the system change number (SCN). The database must be set to Automatic Undo Management work (AUM). For this review the following parameters:
UNDO_MANAGEMENT = auto
undo_tablespace = UNDOTBS001 (tablespace that hosts the segment undo)
UNDO_RETENTION = 3600 (time in seconds that we retained the data in the segment undo)
Must be taken into account with respect to the parameter if the UNDO_RETENTION UNDO tablespace is not large enough to keep all transactions that time, the database manager is going to override.Also, consider that in order to execute the command flashback query must have permissions on the package BDMS_FLASHBACK. To do this:

sys> grant execute on dbms_flashback to user1;

Here is an example. Suppose you want to inquire about a table that contains bills cllowing and want to access the customer data we have previously deleted:

sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema;

fecha_sistema
-------------------------
12-09-2011 12:05
sys> delete from t_facturas WHERE cod_cliente = '00125 ';
4 rows deleted
sys> commit;

  

Half an hour later by running:

sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema
fecha_sistema
------------------------- 12-09-2011 12:35

sys> exec dbms_flashback.enable_at_time (to_date ('12-09-2011 12:05, 'DD-MM-YYYY HH24: MI'));

sys> select to char (sysdate, 'dd-mm-yyyy hh24: mi') from dual fecha_sistema
fecha_sistema
-------------------------
12-09-2011 12:35

sys> exec dbms_flashback.enable_at_time (to_date ('12-09-2011 12:05, 'DD-MM-YYYY HH24: MI'));

PL / SQL procedure successfully completed.

sys> select * from WHERE t_facturas cod_cliente = '00125 ';

... ...

... ...

... ...

... & Hellip;

4 selected records.

sys> execute dbms_flasback.disable;

sys> select count (*) from t_facturas WHERE cod_cliente = '00125 ';

count (*)

------

0

Can be obtained by accessing the same change number SCN:

sys> dbms_flashback.get_system_change_number select from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1307125

sys> exec dbms_flashback.enable_at_system_change_number (1307125);

PL / SQL procedure successfully completed

 

It is also possible to use the statement 'select ...as of ... ':

sys> select * from WHERE t_facturas cod_cliente = '00125 'TO_TIMESTAMP as of timestamp ('12-09-2011 12:05', 'DD-MM-YYYY HH24: MI');

sys> select * from WHERE t_facturas cod_cliente = '00125 'as of scn 1307125;

Keep in mind that while the session is in Flashback Query mode, we can only execute SELECT statements. The update statements (insert, delete and update) are not allowed.

In the daily work with this option is useful to use temporary tables to work with the retrieved data:

sys> create table t_facturas_antas (select * from WHERE t_facturas cod_cliente = '00125 'TO_TIMESTAMP as of timestamp ('12-09-2011 12:05,' DD-MM-YYYY HH24: MI ');

Other options Flashback Query

Below are some features that Oracle provides operations related to Flashback Query:
  • Flashback Version Query: Access to historic changes to a table.
  • Flashback Transaction Query: Access to historical changes in a particular transaction.
  • Flashback Table: Access to data above, but for a single table.
  • Flashback Drop: Retrieve a deleted table ('Recycle Bin').
  • Flashback Database: Lets leave the DB as it was in a past time.Similar to restore a backup, but with the time constraints of the processes flashback, but much faster to retrieve the backup copy. You must have enabled flashback mode and the flash recovery area.

Some of these options require that the manager is 'Enterprise Edition'.

It is, as mentioned, a very useful statement with multiple options "rewind" and that we can take more than a pinch.