Transactions of the Flashback Query operation

Flashback Query: queries the information of records stored in objects at a certain time point in the past, between the current time and a specified time point in the past.
 
SQL> conn scott/Oracle
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
1196559
 
SQL> update flash_tbl set id = id + 100 where id> 15;
 
5 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> delete flash_tbl where id <5;
 
4 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> desc flash_tbl
Name Null? Type
-----------------------------------------------------------------------------
ID NUMBER
VL VARCHAR2 (1)
 
SQL> insert into flash_tbl values (300, 'R ');
 
1 row created.
 
SQL> insert into flash_tbl values (500, 'T ');
 
1 row created.
 
SQL> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
1196625
 

SQL> select id, vl, versions_startscn, versions_endscn, versions_operation, versions_xid from flash_tbl versions between scn 1196559 and 1196625;
 
Id v VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
------------------------------------------------------------
120 S 1196576 U 0700290074010000
119 R 1196576 U 0700290074010000
118 Q 1196576 U 0700290074010000
117 P 1196576 U 0700290074010000
116 O 1196576 U 0700290074010000
10 I
11 J
12 K
13 L
14 M
15 N
 
Id v VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
------------------------------------------------------------
16o 1196576
17 P 1196576
18 Q 1196576
19 R 1196576
20 S 1196576
4 C 1196588 D 08000B0096010000
3 B 1196588 D 08000B0096010000
2 A 1196588 D 08000B0096010000
1/1196588 D 08000B0096010000
1/1196588
2 A 1196588
 
Id v VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
------------------------------------------------------------
3 B 1196588
4 C 1196588
5 D
6 E
7 F
8 GB
9 H
 
29 rows selected.
 

Based on the record, we can see the start SCN and end SCN. The operation columns include I (insert), U (update), D (delete), and transaction ID.
 
SQL> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
1200548
 
SQL> delete flash_tbl where id = 116;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
1200555
SQL> conn/as sysdba
Connected.
SQL> grant select any transaction to scott;
 
Grant succeeded.
 
SQL> conn scott/oracle
Connected.
 
SQL> select xid, commit_scn, commit_timestamp, operation, undo_ SQL from flashback_transaction_query Q where Q. xid in (select versions_xid from flash_tbl versions
 
Scn 1200548 and 1200555 );
 
XID COMMIT_SCN COMMIT_TI OPERATION
-------------------------------------------------------------------
UNDO_ SQL
--------------------------------------------------------------------------------
0a002f00620100000 1200554 31-MAR-14 DELETE
Insert into "SCOTT". "FLASH_TBL" ("ID", "VL") values ('20170101', 'O ');
 
0a002f00620100000 1200554 31-MAR-14 BEGIN
 
As shown in the preceding figure, the operations and time just done are similar to the LogMiner function.