Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will be explaining how we can use the Flashback Version Query to provide a transaction ID that locates the specific transaction to e reversed.
1. We query the data in the table to be changed.
1 2 3 4 5 6 7 8 | [Primary-2] SQL> select * from hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa |
2. DML operations are performed on the table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Primary-2] SQL> update hr.regions set region_name='Middle East' where region_id=4; 1 row updated. [Primary-2] SQL> commit; Commit complete. [Primary-2] SQL> insert into hr.regions values (5,'Africa'); 1 row created. [Primary-2] SQL> commit; Commit complete. |
3. SQL*Plus variable is set to see the time.
1 | [Primary-2] SQL> set time on |
4. Observe how the data in the table changes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 14:13:26 [Primary-2] SQL> select versions_xid xid, 14:13:47 2 versions_operation operation, 14:13:47 3 to_char(versions_starttime,'DD-MON-RR HH24:MI:SS') starttime, 14:13:47 4 to_char(versions_endtime,'DD-MON-RR HH24:MI:SS') endtime, 14:13:47 5 region_name 14:13:47 6 from hr.regions 14:13:47 7 versions between timestamp 14:13:47 8 to_timestamp('23-MAY-18 14:05:00','DD-MON-RR HH24:MI:SS') 14:13:47 9 and systimestamp 14:13:47 10 where region_id in (4,5); XID O STARTTIME ENDTIME REGION_NAME ---------------- - --------------------------- --------------------------- ------------------------- 14000C0020220000 U 23-MAY-18 14:09:16 Middle East 23-MAY-18 14:09:16 Middle East and Africa 11001F0028110000 I 23-MAY-18 14:09:59 Africa |
Description of the above output:
It is not known since when the value of Middle East and Africa, whose Region_id is 4, has been this way. Updated on 23-MAY-18 14:09:16 and the value was changed to Middle East. The ID of the transaction is 14000C0020220000. INSERT was performed on 23-MAY-18 14:09:59 with the transaction ID 11001F0028110000.
