昨天有一个网友问我，怎么能够查询一个表中最后一条插入的记录，我大概回复了，可以通过闪回事务来实现，但是得看什么时候插入的数据，也需要一定的运气。
如果通过闪回事务来得到对应的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