oracle-闪回

简介

作用:
错误的增删改,并commit; 错误的删除表;
获取表上的历史操作记录;
撤销已经提交的事务
类型:
闪回表中的数据:将表回退到过去的一个时间点上
闪回删除的表:还原Oracle回收站
闪回事务
闪回数据库(了解):将数据库回退到过去的一个时间点上
闪回归档日志(了解)

闪回参数设置

查看闪回参数:

sysdba登录,show parameter undo ;

SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

修改闪回时间:

alter system set undo_retention=1200 scope=both;
超出3912M的数据,在1200秒内可以闪出。

scope:
memory:内存。修改只改 当前数据库;重启后仍然是900秒。
spfile:文件。只改物理文件,不改当前数据库;重启后才变为1200秒。
both:以上二者

将表回退到过去的一个时间或SCN上。

闪回表需要授权:
grant flashback any table to scott; 授权闪回所有表的权限给scott用户

计算scn:
select timestamp_to_scn(sysdate) scn from dual ;

示例:

create table testfb
(
id number,
name varchar2(20)
);
insert into testfb values(1,'zhangsan') ;
insert into testfb values(2,'lisi') ;
insert into testfb values(1,'zhangsan') ;

记录此时的时间 select timestamp_to_scn(sysdate) scn from dual ;

删除数据并提交:

delete from testfb where id=1;
commit ;

闪回表:
flashback table testfb to scn 12312 ;

闪回报错

SQL> flashback table testfb to scn 1195871;
flashback table testfb to scn 1195871
*
1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表
SQL> alter table testfb enable row movement ; -- 报错:开启行移动
表已更改。
SQL> flashback table testfb to scn 1195871;
闪回完成。
SQL>

注意:
1.要闪回表,必须知道闪回的时间-->SCN,如何知道闪回的时间?
2.系统表(数据字典)不能被闪回
3.闪回表不能跨越DDL(create table…)

管理员没有回收站,无法闪回。登录sys 演示
ORA-08185: 用户 SYS 不支持闪回

闪回删除的表(还原回收站中的表)

drop table testfb;删除表

show recyclebin; 查看回收站

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS BIN$HB5wdBWnQnmjR2EVrXaMxg==$0 TABLE 2022-07-17:00:32:32
DEPT BIN$FmyIP67ZSjmqUaSK20QN4A==$0 TABLE 2022-07-17:00:32:15
EMP BIN$RtL2dYlURHKyjDkbWGepmA==$0 TABLE 2022-07-17:00:32:07
SALGRADE BIN$AuXxE/UzQDueaotLzuWmTA==$0 TABLE 2022-07-17:00:59:45
SALGRADE BIN$Ne5pjI+gRn6PQS079HSqEQ==$0 TABLE 2022-07-17:00:32:44
STUDENT BIN$2Me6JX/lRzaNU5STr8fifg==$0 TABLE 2022-07-17:00:59:26
TESTFB BIN$p0uIW1njR1CJAhmiOvsfpQ==$0 TABLE 2022-07-17:12:19:13
SQL>

闪回表:
flashback table 表原名 to before drop;
默认情况下:如果回收站中存在多个同名的表,使用表名闪回 闪回的是最近一次删除的表

如果闪回的表和库中目前的表重名,则冲突,必须重命名rename to ...
flashback table testfb to before drop rename to test2

指定闪回某个表
flashback table '回收站中的名字' to before drop ;

使用回收站名称闪回表,需要加单引号

purge recyclebin; --清空回收站

drop table 表名 purge ; -- 直接彻底删除一张表(不过回收站)

闪回表是否恢复触发器:
flashback table 表名 to before drop [rename to 新表名] enable|disable(默认) triggers;

闪回事务

事务闪回需要在数据字典中根据事务号查询undo_sql,然后执行nudo_sql闪回

准备数据

insert into testfb values(1,'zhangsan1') ;
insert into testfb values(2,'zhangsan2') ;
insert into testfb values(3,'zhangsan3') ;
commit; --第1个版本
insert into testfb values(4,'zhangsan4') ;
insert into testfb values(5,'zhangsan5') ;
insert into testfb values(6,'zhangsan6') ;
commit; --第2个版本
insert into testfb values(7,'zhangsan7') ;
insert into testfb values(8,'zhangsan8') ;
insert into testfb values(9,'zhangsan9') ;
commit; --第3个版本

查询事务号(需要使用 伪列)

select id,name,versions_xid,versions_operation,versions_starttime,versions_endtime from testfb versions between timestamp minvalue and maxvalue;

SQL> select id,name,versions_xid,versions_operation,versions_starttime,versions_endtime from testfb versions between timestamp minvalue and maxvalue;
ID NAME VERSIONS_XID VE VERSIONS_STARTTIME VERSIONS_ENDTIME
---------- ---------------------------------------- ---------------- -- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
6 zhangsan6 02000A004E030000 I 19-7-22 01.09.43 上午
5 zhangsan5 02000A004E030000 I 19-7-22 01.09.43 上午
4 zhangsan4 02000A004E030000 I 19-7-22 01.09.43 上午
3 zhangsan3 0700110090020000 I 19-7-22 01.09.16 上午
2 zhangsan2 0700110090020000 I 19-7-22 01.09.16 上午
1 zhangsan1 0700110090020000 I 19-7-22 01.09.16 上午

versions_xid(事务号)
select id,name from testfb versions between timestamp minvalue and maxvalue ; --查询所有版本

查询undo_sql

select operation,undo_sql from flashback_transaction_query where xid='事务号';

SQL> select operation,undo_sql from flashback_transaction_query where xid='02000A004E030000';
OPERATION
----------------------------------------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT
delete from "SCOTT"."TESTFB" where ROWID = 'AAASRDAAEAAAAJdAAF';
INSERT
delete from "SCOTT"."TESTFB" where ROWID = 'AAASRDAAEAAAAJdAAE';
INSERT
delete from "SCOTT"."TESTFB" where ROWID = 'AAASRDAAEAAAAJdAAD';
OPERATION
----------------------------------------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
  1. 权限不足
    select operation,undo_sql from flashback_transaction_query where xid='03001F007E030000'
    第 1 行出现错误:
    ORA-01031: 权限不足
    grant select any transaction to scott;
  2. 查询undo_sql全部显示UNKNOWN
    未开启日志,闪回事务 实际上 借助的是日志,未开启日志之前提交的事务不支持回滚
    alter database add supplemental log data;

执行operate对应的undo_sql

全部撤销需要执行该事务号对应的全部的undo_sql,如果只执行某一条undo_sql,则只撤销该条undo_sql对应的dml

posted @   姬雨晨  阅读(870)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 百万级群聊的设计实践
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 全网最简单!3分钟用满血DeepSeek R1开发一款AI智能客服,零代码轻松接入微信、公众号、小程
· .NET 10 首个预览版发布,跨平台开发与性能全面提升
· 《HelloGitHub》第 107 期
点击右上角即可分享
微信分享提示