公众号：杨建荣的学习笔记 · 作者：r2笔记69天 · 发布：2014-08-19 21:04:00 · 原文链接

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