透過閃回事務檢視資料dml的情況

昨天有一個網友問我，怎麼能夠查詢一個表中最後一條插入的記錄，我大概回覆了，可以透過閃回事務來實現，但是得看什麼時候插入的資料，也需要一定的運氣。
如果透過閃回事務來得到對應的undo_sql，可能多個dml語句對應一個事務，所以我們需要得到的是一個完整的事務的資訊，裡面包括對應的Undo_sql，這樣才算得到比較完整的sql語句。

我在本地自己做了一個測試。
建立一個test表，然後插入一些記錄，然後嘗試修改一些資料。

SQL> DROP TABLE TEST;
Table dropped.

SQL> create table test (id number,name varchar2(29));
Table created.

SQL> insert into test values(1,'trx1');
1 row created.

SQL> insert into test values(2,'trx1');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into test values(3,'trx2');
1 row created.

SQL> commit;
Commit complete.

SQL> update test set id=4,name='trx3' where name='trx1';
2 rows updated.

SQL> commit;
Commit complete.

透過閃回查詢，能夠得到一些相關的資訊。I代表insert,U代表update，因為是測試，我直接把列值也列出來了。

SQL> set linesize 200
SQL> col VENDTIME format a22
SQL> col V_STARTTIME format a22
SQL> select versions_starttime v_starttime,versions_startscn v_startscn,versions_xid xid,versions_endtime vendtime,versions_endscn vendscn,versions_operation oper,id,name from test versions between scn minvalue and maxvalue;

V_STARTTIME            V_STARTSCN XID              VENDTIME                  VENDSCN O         ID NAME
---------------------- ---------- ---------------- ---------------------- ---------- - ---------- -----------------------------
19-AUG-14 05.30.14 AM     7233099 0A000F0070040000                                   U          4 trx3
19-AUG-14 05.30.14 AM     7233099 0A000F0070040000                                   U          4 trx3
19-AUG-14 05.29.44 AM     7233087 08001B00FD060000                                   I          3 trx2
19-AUG-14 05.29.38 AM     7233084 0700100091040000 19-AUG-14 05.30.14 AM     7233099 I          2 trx1
19-AUG-14 05.29.38 AM     7233084 0700100091040000 19-AUG-14 05.30.14 AM     7233099 I          1 trx1

得到了如上的資訊之後，就開始使用閃回事務來查詢，結果沒有任何返回。

SQL> select operation,undo_sql from flashback_transaction_query where table_name='TEST' and table_owner='N1';

no rows selected

檢視Undo的空間情況，undo還很充足。

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3645037571 TEST01              1 TEST01

Tablespace   STA M A Init     Total MB    Free MB     Used MB  LrgstMB       MaxExt %Fr A
------------ --- - - ---- ------------ ---------- ----------- -------- ------------ --- -
POOL_DATA    OLN L S  64K          960        765         195       98   2147483645  80
SYSAUX       OLN L S  64K          325         96         229       91   2147483645  30
SYSTEM       OLN L S  64K          325         51         274       50   2147483645  16 *
TEMPTS1      OLN L U   1M          400        400           0      286              100
TEST_DATA1   OLN L S  64K            3          2           1        2   2147483645  67
UNDOTBS      OLN L S  64K          935        903          32      672   2147483645  97
                          ------------ ---------- -----------
sum                              2,948      2,217         731

使用閃回事務需要賦予一定的許可權，賦一下許可權。
使用dba連線，賦予許可權

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_flashback to n1;

Grant succeeded.

SQL> grant select any transaction to n1;
Grant succeeded.

再次查詢驗證，還是沒有資料。

SQL> select operation,undo_sql from flashback_transaction_query where table_name='TEST' and table_owner='N1';

no rows selected

直接用sys看看，倒底是怎麼回事，能看到Operation 顯示為“unknown"而且對應的table_owner也是空。

conn / as sysdba
SQL> col undo_sql format a30
SQL> select table_owner,operation,undo_sql from flashback_transaction_query where table_name='TEST'
  2  /

TABLE_OWNER                      OPERATION                        UNDO_SQL
-------------------------------- -------------------------------- ------------------------------
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN
原來，需要使用alter database命令，啟用對DML更改引用的列值和主鍵值的日誌記錄。

SQL> alter database add supplemental log data;

Database altered.

SQL> conn n1/n1
Connected.
啟用之後，不會立即生效，我們來清空資料，重新插入一些資料。
SQL>  select table_owner,operation,undo_sql from flashback_transaction_query where table_name='TEST'
  2  /

TABLE_OWNER                      OPERATION                        UNDO_SQL
-------------------------------- -------------------------------- ------------------------------
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN

SQL> truncate table test;
Table truncated.

SQL> insert into test values(1,'trx1');
1 row created.

SQL> insert into test values(2,'trx1');
1 row created.
SQL> commit;

Commit complete.
再次查詢就能夠成功顯示了。
SQL> col undo_sql format a80
SQL> select table_owner,operation,undo_sql from flashback_transaction_query where table_name='TEST';

TABLE_OWNER                      OPERATION                        UNDO_SQL
-------------------------------- -------------------------------- --------------------------------------------------------------------------------
N1                               INSERT                           delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAB';
N1                               INSERT                           delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAA';
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN
                                 UNKNOWN
解決了這個問題，就可以使用閃回事務表來檢視undo_sql了，我們根據start_timestamp來排列，得到最新的資料變化情況。
先使用閃回查詢來看看。

SQL> select versions_starttime v_starttime,versions_startscn v_startscn,versions_xid xid,versions_endtime vendtime,versions_endscn vendscn,versions_operation oper,id,name from test versions between scn minvalue and maxvalue;

V_STARTTIME            V_STARTSCN XID              VENDTIME                  VENDSCN O         ID NAME
---------------------- ---------- ---------------- ---------------------- ---------- - ---------- -----------------------------
19-AUG-14 05.36.08 AM     7247547 05001A00C2050000                                   I          2 trx1
19-AUG-14 05.36.08 AM     7247547 05001A00C2050000                                   I          1 trx1

然後透過閃回事務來關聯。

SQL> select table_owner,START_TIMESTAMP,operation,undo_sql from flashback_transaction_query where table_name='TEST' and table_owner='N1' and xid=hextoraw('05001A00C2050000'); 

TABLE_OWNER                      START_TIM OPERATION                        UNDO_SQL
-------------------------------- --------- -------------------------------- --------------------------------------------------------------------------------
N1                               19-AUG-14 INSERT                           delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAB';
N1                               19-AUG-14 INSERT                           delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAA';

在做undo之前，檢視錶裡現有的資料作比對。

SQL> select *from test;

        ID NAME
---------- -----------------------------
         1 trx1
         2 trx1

 

SQL> delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAB';
1 row deleted.

SQL> delete from "N1"."TEST" where ROWID = 'AAAEmOAAJAAABRjAAA';
1 row deleted.

SQL> select *from test;

no rows selected