闪回表和闪回删除都是逻辑层面的闪回技术,都是通过flashback table的语法来实现。
语法如下:
闪回表是利用undo数据来使得表恢复到特定的时间(SCN、timestamp、restore point)的状态。同时恢复的过程中会同步更新索引,触发触发器,还有约束。不过,闪回表也有一些限制。
1、权限限制。需要有下面这些权限:
---授予对表的闪回权限
SQL> grant flashback on sh.sales to sh;--闪回特定表的权限;
SQL> grant flashback any tables to sh;--闪回所有表的权限;
---授予对该表的DML操作和ALTER权限
SQL> grant select,update,delete,alter on sh.sales to sh;
---如果需要闪回到还原点,还需要下面的权限或角色
SQL> grant flashback any tables to sh;--闪回所有表的权限;
SQL> grant select any dictionary to sh;
SQL> grant select_catalog_role to sh;
2、表限制
如果该表是聚集表、物化视图、AQ表、基表、系统表、远程表、对象表、嵌套表或者分区表的分区或子分区,都不能进行闪回表操作。
还有就是,在闪回时间到当前时间的区间内,该表的结构不能有所变化。
该表必须启动行移动,因为rowid会有可能改变。
SQL>alter table sales enable row movement;
还有就是, 撤销段内必须有足够的数据满足闪回表的需要。
SQL> show parameter undo_retention;---查看撤销段数据块的保留时间,系统默认为15分钟
NAME TYPE VALUE
------------------------------------ ----------- --------
undo_retention integer 900
SQL> alter system set undo_retention=86400 scope =spfile;---修改undo_retention,建议为24小时
3、具体操作
闪回表的时候,可以闪回到指定的时间、SCN或者restore point。另外,闪回的时候默认是不会触发触发器,如果需要的必须明确enable trigger。
SQL> create table t(
2 id int,
3 num int);
表已创建。
SQL> insert into t
2 select 1,100 from dual union all
3 select 2,200 from dual;
已创建2行。
SQL> commit;
提交完成。
SQL> select current_scn from v$database;---当前的SCN为2591273
CURRENT_SCN
-----------
2591273
SQL> update t set num =300 where id =1;---更新数据行
已更新 1 行。
SQL> commit;
提交完成。
SQL> alter table t enable row movement;---启动行移动
表已更改。
SQL> flashback table t to scn 2591273;---闪回表到SCN为2591273的状态,也就是更新前
闪回完成。
SQL> select id,num from t where id =1;---查询闪回成功
ID NUM
---------- ----------
1 100
SQL>
闪回到timestamp的形势差不多,下面使用restore point。
SQL> create restore point resp;---创建还原点
还原点已创建。
SQL> select name,scn,time from v$restore_point;---查看还原点
NAME SCN TIME
------ ---------- -----------------------------------
RESP 2592127 08-8月 -13 04.56.57.000000000 下午
SQL> update t set num=500 where id =2;---修改数据
已更新 1 行。
SQL> commit;
提交完成。
SQL> flashback table t to restore point resp;---闪回到指定还原点
闪回完成。
SQL> select * from t where id =2;----查看结果
ID NUM
---------- ----------
2 200
另外,若有闪回的时候引发触发器,则需要附加enable trigger启动;
SQL> flashback table t to scn 2604979 enable trigger;---启动触发器
SQL> flashback table t to scn 2604979 disable trigger;---关闭触发器