Flashbackquery transactions for query operations

Flashback query: Queries the record information that has been saved in the past at a point in time, 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 (+, ' r ');

1 row created.

sql> INSERT into FLASH_TBL values (' 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

---------- - ----------------- --------------- - ----------------

0700290074010000 S 1196576 U

119 R 1196576 U 0700290074010000

118 Q 1196576 U 0700290074010000

117 P 1196576 U 0700290074010000

116 O 1196576 U 0700290074010000

Ten I

One J

K

L

M

N

ID v versions_startscn VERSIONS_ENDSCN v Versions_xid

---------- - ----------------- --------------- - ----------------

O 1196576

P 1196576

Q 1196576

R 1196576

1196576 S

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 G

9 H

Rows selected.

Depending on the record, you can see the start SCN and the end SCN, from the Action column have 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 between

SCN 1200548 and 1200555);

XID COMMIT_SCN Commit_ti OPERATION

---------------- ---------- --------- --------------------------------

Undo_sql

--------------------------------------------------------------------------------

0a002f0062010000 1200554 31-mar-14 DELETE

INSERT INTO "SCOTT". " Flash_tbl "(" ID "," VL ") VALUES (' 116 ', ' O ');

0a002f0062010000 1200554 31-mar-14 BEGIN

Through the above can see, just do the operation and time, and Logminer function quite like.