Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain the use of the Flashback transaction query to get extra information about the transactions listed by the flashback version queries.
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 occur 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 |
5. New updates are made.
1 2 3 4 5 6 7 | 14:16:33 [Primary-2] SQL> update hr.regions set region_name='Afrika' where region_id=5; 1 row updated. 14:17:32 [Primary-2] SQL> commit; Commit complete. |
6. Let’s find out who made the changes and UNDO_SQL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 14:17:34 [Primary-2] SQL> select versions_xid xid, 14:17:50 2 versions_operation operation, 14:17:50 3 to_char(versions_starttime,'DD-MON-RR HH24:MI:SS') starttime, 14:17:50 4 to_char(versions_endtime,'DD-MON-RR HH24:MI:SS') endtime, 14:17:50 5 region_name 14:17:50 6 from hr.regions 14:17:50 7 versions between timestamp 14:17:50 8 to_timestamp('23-MAY-18 14:05:00','DD-MON-RR HH24:MI:SS') 14:17:50 9 and systimestamp 14:17:50 10 where region_id in (4,5); XID O STARTTIME ENDTIME REGION_NAME ---------------- - --------------------------- --------------------------- ------------------------- 14000C0020220000 U 23-MAY-18 14:09:15 Middle East 23-MAY-18 14:09:15 Middle East and Africa 140003001B220000 U 23-MAY-18 14:17:32 Afrika 11001F0028110000 I 23-MAY-18 14:09:59 23-MAY-18 14:17:32 Africa 14:26:47 [Primary-2] SQL> column undo_sql format a50 14:27:10 [Primary-2] SQL> select operation, 14:27:38 2 logon_user, 14:27:38 3 undo_sql 14:27:38 4 from flashback_transaction_query 14:27:38 5 where xid = HEXTORAW('140003001B220000'); OPERATION LOGON_USER UNDO_SQL -------------------------------- ------------------------------ -------------------------------------------------- UNKNOWN SYS BEGIN SYS |
The reason why OPERATION is UNKNOWN is because there is not enough UNDO data. For this reason, some parameters are set.
7. Let’s set the parameters where we can see UNDO_SQL.
1 2 3 4 5 6 7 8 9 10 11 | 14:27:39 [Primary-2] SQL> alter database add supplemental log data; Database altered. 14:29:23 [Primary-2] SQL> alter database add supplemental log data (primary key) columns; Database altered. 14:29:29 [Primary-2] SQL> alter database add supplemental log data (foreign key) columns ; Database altered. |
8. A new DML is run.
1 2 3 4 5 6 7 | 14:30:31 [Primary-2] SQL> update hr.regions set region_name='Africa' where region_id=5; 1 row updated. 14:30:37 [Primary-2] SQL> commit; Commit complete. |
9. The changes made are questioned.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 14:30:41 [Primary-2] SQL> select versions_xid xid, 14:30:47 2 versions_operation operation, 14:30:47 3 to_char(versions_starttime,'DD-MON-RR HH24:MI:SS') starttime, 14:30:47 4 to_char(versions_endtime,'DD-MON-RR HH24:MI:SS') endtime, 14:30:47 5 region_name 14:30:47 6 from hr.regions 14:30:47 7 versions between timestamp 14:30:47 8 to_timestamp('23-MAY-18 14:05:00','DD-MON-RR HH24:MI:SS') 14:30:47 9 and systimestamp 14:30:47 10 where region_id in (4,5); XID O STARTTIME ENDTIME REGION_NAME ---------------- - --------------------------- --------------------------- ------------------------- 14000C0020220000 U 23-MAY-18 14:09:15 Middle East 23-MAY-18 14:09:15 Middle East and Africa 120008003B110000 U 23-MAY-18 14:30:37 Africa 140003001B220000 U 23-MAY-18 14:17:33 23-MAY-18 14:30:37 Afrika 11001F0028110000 I 23-MAY-18 14:09:59 23-MAY-18 14:17:33 Africa |
10. UNDO_SQL is displayed.
1 2 3 4 5 6 7 8 9 10 11 12 | 14:30:48 [Primary-2] SQL> select operation, 14:31:10 2 logon_user, 14:31:10 3 undo_sql 14:31:10 4 from flashback_transaction_query 14:31:10 5 where xid = HEXTORAW('120008003B110000'); OPERATION LOGON_USER UNDO_SQL -------------------------------- ------------------------------ -------------------------------------------------- UPDATE SYS update "HR"."REGIONS" set "REGION_NAME" = 'Afrika' where ROWID = 'AAAVS5AAFAAAACLAAA'; BEGIN SYS |
