而通过Flash Transaction Query则可以利用xid来获得回滚的sql statement. 用户可以根据需要利用这些undo sql来回滚数据.
所以 Flash Transaction Query 实际上是 Flash versions query 的扩展. 通常会配合使用.
例子:
例用上面的表, 新插入两条重复的数据.
SQL> insert into test3 select 4, 'Paula' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test3 select 4, 'Paula' from dual;
1 row created.
SQL> commit;
Commit complete.
通过Flash versions query 可以查询到xid:
select id, name, versions_xid
from Test3 versions between scn minvalue and maxvalue where id > 3;
ID NAME VERSIONS_XID
---------- ---------- ----------------
4 Paula 01001800D8060000
4 Paula 030013006A090000
而通过 Flash transaction query 则可以获得回滚sql.
select operation, undo_sql from flashback_transaction_query
where xid in (select versions_xid from Test3 versions between scn minvalue and maxvalue where id > 3)
输出:
OPERATION UNDO_SQL
-------------------------------- ------------------------------------------------------------------------------------------------
INSERT delete from "BILL"."TEST3" where ROWID = 'AAASf4AAFAAACkXAAE';
BEGIN
INSERT delete from "BILL"."TEST3" where ROWID = 'AAASf4AAFAAACkXAAD';
BEGIN
可以见到, insert 语句的 Undo sql是delete 语句, 而且是用Rowid来作为condition的 所以, 即使我插入两条完全相同的数据行, 也可以正确地回滚.
接下来是Flash Query.
上面介绍的Flash versions query 和 Flash transaction query 都是查看每个commited的事务具体信息.
而Flash Query是查看某张table 在某个时刻的数据镜像, 依赖于undo表空间的undo数据.
一旦我们发现某个时间的数据满足我们的需求之后, 我们就可以例用该镜像来回滚整张table.
Flashback Query 利用多版本读一致性从undo tablesapce 读取操作前的记录数据. 可以用于进来数据对比或覆盖回滚.
所谓多版本读一致性就是:
Oracle 采用了一种设计, 同过undo数据来保证写操作不影响读操作. 简单地讲, A事务正在写数据时, 回将数据的前映像写入undo表空间, 如果B事务读取同样的数据, 则会读到undo表空间的正确数据, 而不需要等A事务commit or rollbak. 这样保证了B事务不会读到A事务未就提交的数据.
Flashback Query 有多种方式构建查询recordset, 其选择范围可以基于时间(As of timpstamp) or 基于scn(As of scn).
语法:
使用 as of scn
SELECT <column_name_list>
FROM <table_name>
AS OF <SCN>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
使用 as of timestamp
SELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
5.1 As of timestamp 例子:
继续用上面的表test3做例子:
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-05-24 22:44:21
SQL> select * from test3;
ID NAME
---------- ----------
1 Jack
2 Billing
4 Paula
4 Paula
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-05-24 22:46:16
SQL> update test3 set name = 'Jason';
4 rows updated.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-05-24 22:48:30
SQL> delete from test3;
4 rows deleted.
SQL> commit;
Commit complete.
解析:
1.在 22:44 时, Test3 表里有4行数据.
2.在 22:46 时, 把Test3 表全部更新成name = Jason
3. 在 22:48 时, 删除所有记录(模拟误删除).
假如当前时间是22:50 , 操作者想查看6分钟前和3分钟前的表映像:
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-05-24 22:50:54
SQL> select * from test3 as of timestamp sysdate - 6/1440;
ID NAME
---------- ----------
1 Jack
2 Billing
4 Paula
4 Paula
SQL> select * from test3 as of timestamp sysdate - 3/1440;
ID NAME
---------- ----------
1 Jason
2 Jason
4 Jason
4 Jason
假如操作者想把表恢复成6分钟前的版本
则配合insert操作就ok了, 注意下面timestamp就制定了具体值, 大概22:44 .
SQL> insert into test3 select * from test3 as of timestamp to_timestamp('2014-05-24 22:44:05','YYYY-MM-DD hh24:mi:ss');
4 rows created.
SQL> commit;
Commit complete.
SQL> select * from test3;
ID NAME
---------- ----------
1 Jack
2 Billing
4 Paula
4 Paula
上面例子, 表明as of timestamp的确非常易用, 但是大部分情况吓, 我们建议使用as of scn来执行flashback query.
因为需要对多个有主外键约束的表进行恢复时, 如果使用as of timestamp 可能因为时间点不统一而导致插入失败.
5.2 As of scn 例子:上面例子我们利用
select sysdate from dual;
来获得数据库当前时间.
相应地, 我们可以利用
select current_scn from v$database;
来获得数据库的当前scn, 注意必须具有select any dictionary 的权限
下面是例子:
首先获得当前数据库scn
然后删除test3 所有数据行:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3088322
SQL> delete from test3;
4 rows deleted.
SQL> commit;
Commit complete.
然后利用as of scn flashback query来获得具体scn时的test3映像:
SQL> select * from test3 as of scn 3088322;
ID NAME
---------- ----------
1 Jack
2 Billing
4 Paula
4 Paula
配合insert into语句恢复表数据:
SQL> select * from test3 as of scn 3088322;
ID NAME
---------- ----------
1 Jack
2 Billing
4 Paula
4 Paula
SQL> insert into test3 select * from test3 as of scn 3088322;
4 rows created.
SQL> commit;
Actually, 无论用户使用as of timestamp or scn, oracle在底层都是使用scn. 也就是oracle具有1个把timestamp转换成scn的机制.
oracle里有一张表来表示timestamp 和 scn 的对应关系. 这张表就是sys.smon_scn_time.
oracle每隔5分钟, 系统产生一次系统时间标记与scn的匹配记录并存入sys.smon_scn_time.
值得注意的时, 也就是在这5分钟所有的时间都是匹配同1个scn. 也就是说使用as of timestamp 5分钟内的不同timpstamp实际上都是指向同1个scn..
可以用如下语句来查看scn 和 timestamp之间的对应关系:
select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time;
5.3 伪列 ORA_ROWCNOracle为了方便维护会为数据表添加一些内部字段, 我们最熟悉的伪列就是 ROWID.啦. 而ora_rowscn 是 oracle 10g 新增的, 暂且可以把它看做是数据行最后一次被修改的scn. Flash version query 就是通过这个伪列来trace 数据行变化的历史.
例子:
SQL> select ora_rowscn, id , name from test3;
ORA_ROWSCN ID NAME
---------- ---------- ----------
3088585 1 Jack
3088585 2 Billing
3088585 4 Paula
3088585 4 Paula
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3101850
SQL> update test3 set name = 'Calvin' where id = 2;
1 row updated.
SQL> select ora_rowscn, id , name from test3;
ORA_ROWSCN ID NAME
---------- ---------- ----------
3088585 1 Jack
3088585 2 Calvin
3088585 4 Paula
3088585 4 Paula
SQL> commit;
Commit complete.
SQL> select ora_rowscn, id , name from test3;
ORA_ROWSCN ID NAME
---------- ---------- ----------
3101948 1 Jack
3101948 2 Calvin
3101948 4 Paula
3101948 4 Paula
上面的例子, 我只更新了1个数据行, 但是所有行的ora_rowscn都被更新了?
实际上, ora_rowscn默认是数据块(block) 级别的, 也就是说在同1个block中所有数据行都是同1个ora_rowscn. block中任意一行被修改, 该block中所有数据行的ora_rowscn 都会被刷新.
可以在建表时使用keyword rowdependencies, 可以改变ora_rowscn 的默认level, 令每一数据行都有独立的ora_rowscn.