DB - Flashback Query

English Version 

Neste post partilho como ver alterações nos dados e recuperar dados alterados pos commit, se foram alterados recentemente e possível recupar esses dados facilmente usando flasback query e flashback query version.

SQL> create table regions_4_query as select * from regions;
Table created.

SQL> delete regions_4_query where region_id = 1;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select * from regions_4_query;

 REGION_ID REGION_NAME
---------- -------------------------
         2 Americas
         3 Asia
         4 Middle East and Africa


SQL> col versions_starttime form a25 
col versions_endtime form a25

select  versions_startscn, versions_starttime, 
versions_endscn, versions_endtime,versions_xid, versions_operation,
region_id, region_name
from regions_4_query
versions between scn minvalue and maxvalue
where 
region_id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME        VERSIONS_ENDSCN VERSIONS_ENDTIME          VERSIONS_XID     V  REGION_ID REGION_NAME
----------------- ------------------------- --------------- ------------------------- ---------------- - ---------- -------------------------
           825927 14-FEB-18 04.15.03 PM                                               050003009D020000 D          1 Europe
                                                     825927 14-FEB-18 04.15.03 PM                                 1 Europe

 SQL> select * from regions_4_query as of scn 825926;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> select * from regions_4_query as of scn 825926 where region_id = 1;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe

SQL> insert into regions_4_query select * from regions_4_query as of scn 825926 where region_id = 1;
1 row created.

SQL> commit;
Commit complete.

SQL> select * from regions_4_query;

 REGION_ID REGION_NAME
---------- -------------------------
         2 Americas
         3 Asia
         4 Middle East and Africa
         1 Europe

Obrigado pela leitura.
Espero que este post o tenha ajudado.

Versao Portuguesa 

I'm sharing in this  post  how to check and recover data chaged after after commit, these queryes are used for monitoring propose  and  data recovery, if the data was changed recently ] ist's possible to recover the data  easly using flasback query e flashback query version.

SQL> create table regions_4_query as select * from regions;
Table created.

SQL> delete regions_4_query where region_id = 1;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select * from regions_4_query;

 REGION_ID REGION_NAME
---------- -------------------------
         2 Americas
         3 Asia
         4 Middle East and Africa


SQL> col versions_starttime form a25 
col versions_endtime form a25

select  versions_startscn, versions_starttime, 
versions_endscn, versions_endtime,versions_xid, versions_operation,
region_id, region_name
from regions_4_query
versions between scn minvalue and maxvalue
where 
region_id = 1;

 VERSIONS_STARTSCN VERSIONS_STARTTIME        VERSIONS_ENDSCN VERSIONS_ENDTIME          VERSIONS_XID     V  REGION_ID REGION_NAME
----------------- ------------------------- --------------- ------------------------- ---------------- - ---------- -------------------------
           825927 14-FEB-18 04.15.03 PM                                               050003009D020000 D          1 Europe
                                                     825927 14-FEB-18 04.15.03 PM                                 1 Europe

  
 SQL> select * from regions_4_query as of scn 825926;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> select * from regions_4_query as of scn 825926 where region_id = 1;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe

SQL> insert into regions_4_query select * from regions_4_query as of scn 825926 where region_id = 1;
1 row created.

SQL> commit;
Commit complete.

SQL> select * from regions_4_query;

 REGION_ID REGION_NAME
---------- -------------------------
         2 Americas
         3 Asia
         4 Middle East and Africa
         1 Europe

Thank you for reading.
hope this post was helpful.