ALTER
DATABASE
OPEN
RESETLOGS;
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.