Flashback Query查询操作的事务

来源：互联网发布：windows找不到social 编辑：程序博客网时间：2024/06/10 05:20

Flashback Query：查询过去某个时间点对象中保存的记录信息，在当前时间与指定过去某个时间点之间。

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
---------- - ----------------- --------------- - ----------------
        16 O                           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 G
         9 H

29 rows selected.

根据记录，可以看到开始SCN和结束SCN，从操作列有I（插入），U（更新），D（删除），还有事务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

通过上面可以看到，刚才所做的操作及时间，与LogMiner功能挺像。

0 0