Oracle:多版本和闪回

闪回库

普通用户使用dbms_flashback包，必须通过管理员授权。命令如下:

grant execute on dbms_flashback to scott;

闪存到10分钟之前

DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE-10/1440 )

闪回表：

1. 查询两个小时前的某表数据

select * from business_apply as of timestamp sysdate-2/12
select * from t as of timestamp to_timestamp(‘2013-10-25 08:57:47’, ‘yyyy-mm-dd hh24:mi:ss’);
select sysdate-1/12 from dual;

2. 数据库误删除表之后恢复:

flashback table t to before drop;

---

通过版本号闪回

总结
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) time,
to_char(dbms_flashback.get_system_change_number) scn
from dual;
select * from t as of scn 11220804149;

多版本和闪回:

variable SCN number
exec :scn :=dbms_flashback.get_system_change_number
print scn
select * from guaranty_contract;
delete from guaranty_contract;
select count(*) from guaranty_contract AS OF SCN:scn;

select * from (select count(*) from guaranty_contract),(select count(*) from guaranty_contract as of scn :scn);

flashback table guaranty_contract to scn :scn;

ALTER TABLE guaranty_contract ENABLE ROW MOVEMENT;

select * from (select count(*) from guaranty_contract,(select count(*) from guaranty_contract as of scn :scn)