Flashback Transaction Query Posted by MOHAMMED ABDUL HAMEED on 8:36 AM

It is used to get the historical data of the given transaction along with sql code to undo the changes made to the particular rows.

It basically provide undo statements of sql queries.

In order to unable UNDO_SQL we need add the supplemental log data.

SQL> alter database add supplemental log data;
Database altered.

SQL> select * from tab;

TNAME                  TABTYPE       CLUSTERID
------------------------------------------------------------------
CITY TABLE

SQL> select * from city;

ID NAME
----------------------------
1 India
2 Telangana
3 Pune

SQL> update city set name='Hyderabad' where id=1;

1 Row Updated.

commit;

SQL> select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune

SQL> select operation,undo_sql
from flashback_transaction_query
where TABLE_NAME='CITY';

OPERATION         UNDO_SQL
---------------------------------------------------------------------------
update update "HAMEED"."CITY" set "NAME"='INDIA' where ROWID='AAAS4zAAARESIK54TGFD/AAA';

SQL>select * from city;

SQL> select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune

SQL> delete from city where id=3;
commit;

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana

SQL>select operation,undo_sql
from flashback_transaction_query
where TABLE_NAME='CITY';

OPERATION         UNDO_SQL
---------------------------------------------------------------------------
UPDATE update "HAMEED"."CITY" set "NAME"='INDIA' where ROWID='AAAS4zAAARESIK54TGFD/AAA';

DELETE insert into "HAMEED"."CITY"("ID","NAME") values ('3','pune');

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana

SQL>insert into "HAMEED"."CITY"("ID","NAME") values ('3','pune');
1 row created.
commit;

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune