Flashbackquery transactions for query operations

Source: Internet
Author: User
Tags commit current time

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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.