闪回查询和闪回表

13.5.2闪回查询 (用于 DML 误操作)

利用在 undo tablespace 里已经被提交的 undo block(未被覆盖)，可以通过查询的方式将表里面的记录回到过去某个时间点。
通过设置 undo_retention 参数设置前镜像的保留时间。由于利用的是UNDO表空间里记录的数据被改变前的值，因此数据在UNDO空间中保留多久就尤为重要，其中与之关系最紧密的是 undo_retention参数。
undo_retention 通常默认是900 秒，也就是15 分钟。需要注意是undo_retention 只是指定undo 数据的过期时间，并不是undo 中的数据一定会在undo表空间中保存15 分钟，比如一个新事务刚开始的时候，如果undo 表空间已经被写满，则新事务的数据会自动覆盖已提交事务的数据，而不管这些数据是否已过期。因此，当你创建一个自动管理的undo 表空间时，还要注意其空间大小，要尽可能保证undo 表空间有足够的存储空间。
同时还要注意，undo_retention 中指定的时间一过，已经提交事务中的数据就立刻无法访问，它只是失效，只要不被别的事务覆盖，它会仍然存在，并可随时被flashback 特性引用。如果你的undo表空间足够大，而数据库又不是那么繁忙，那么其undo_retention 参数的值不会受影响，哪怕你设置成1，只要没有事务去覆盖undo 数据，它就会持续有效。因此要注意undo 表空间的大小，保证其有足够的存储空间。如果闪回表时所需要的UNDO数据，由于保留的时间超过了UNDO_RETENTION的所指定的值，从而导致该UNDO数据被其他事务覆盖的话，那么就不能闪回到指定时间了。表空间上指定了retention guarantee选项使UNDO数据在一定时间内不被覆盖。
修改UNDO_RETETION的值命令如下：
alter system set undo_retention=600 scope=both;
启用undo guarantee，代码如下：
alter tablespace undotbs1 retention guarantee;
禁用undo guarantee，代码如下：
alter tablespace undotbs1 retention noguarantee;

【例13-9】建立表 student，插入测试记录后对该表进行删除、更新操作后，使用闪回查询该表各个时期的数据。
建立表，插入测试记录，代码如下：
—第十三章\sjkbf.sql

create table student
(sno int，
sname char(10)，
sage int)；

insert into student
select 1，‘Tom’，21 from dual;
insert into student
select 2，‘Kite’，22 from dual;

insert into student
select 3，‘Bob’，23 from dual;

insert into student
select 4，‘Mike’，24 from dual;
commit;

–查询数据
select * from student;

查询表数据，如图所示13-17所示。

图13-17查询表数据
删除表数据，代码如下：

select to_char(sysdate， ‘yyyy-mm-dd hh24:mi:ss’) from dual;

delete student where sno=1;
commit;
select * from student;

删除数据后，把该表状态称定义为状态1，查询表数据如图13-18所示。

图13-18 删除数据后查询表数据

删除数据后，查询系统时间如图13-19所示。

图13-19删除数据后查询系统书简

查询当前 SCN，代码如下：

select current_scn from v$database;

执行后，SCN如图13-20所示。

图13-20 查询系统SCN

更新表数据并查询表数据，代码如下：
update student set sage=50;
commit;
select * from student;

执行后如图13-21所示。

图13-21 更新数据后查询表数据

更新数据后取系统时间 ，代码如下：

select to_char(sysdate， ‘yyyy-mm-dd hh24:mi:ss’) from dual;

执行后如图13-22所示。

图13-22更新数据后查询系统时间

查询当前 SCN，代码如下：

select current_scn from v$database;

执行后如图13-23所示。

图13-23 更新数据后查询系统SCN
通过系统时间，查询状态1表数据，代码如下：
select * from student as of timestamp to_date(‘2022-11-5 15:02:41’，‘yyyy-mm-dd hh24:mi:ss’);
执行后如图13-24所示。

图13-24 查询系统SCN

通过系统SCN，查询插入记录后表的数据，代码如下：

select * from student as of scn 9865474

执行后如图13-25所示。

图13-25 恢复到表最初的状态

13.5.3闪回表
1.普通方式闪回表
闪回表通常是把表的状态回退到以前的某个时刻或者 SCN 上。(其实向前向后都能闪)。自动恢复相关的属性，包括索引、触发器等。前提是对表启用行移动。
使表能行移动语法如下：
flashback table <table_name> to timestamp | scn
【例13-10】建立表student，删除数据后，使表能移动。然后闪回该表。
建立表，闪回该表代码如下：
–第十三章\sjkbf.sql
– 建表，插入测试记录
create table student
(sno int，
sname char(10)，
sage int);

insert into student
select 1，‘Tom’，21 from dual;
insert into student
select 2，‘Kite’，22 from dual;

insert into student
select 3，‘Bob’，23 from dual;

insert into student
select 4，‘Mike’，24 from dual;
commit;

查看当前数据库的scn，代码如下：
select current_scn from v$database

执行后如图13-26所示。

图13-26 查看 scn

删除表数据，代码如下：
delete student;
commit;
执行后如图13-27所示。

图13-27删除数据后查询表数据

–使表能行移动
alter table student enable row movement;

–根据当时的scn，闪回表。XXXXX代表当时的scn

flashback table student to scn 11678406

使用闪回表时需要注意：
1)sys 的表不能闪回。
2)必须使行能移动。
3)缺省下，闪回表的过程中有关的 trigger 都关闭。
2.使用闪回归档
Oracle 11g 新特性是闪回归档 (FLASHBACK ARCHIVE)。闪回归档概念：无限期的存储表行的前镜像，通过后台进程 FBDA，捕捉必要的数据并将其保存在归档上，然后可以使用常规闪回查询命令(as of)查询需要的数据，闪回可以回朔到多年以前。闪回归档可以看成是闪回查询时间的延伸。
闪回归档有几个要点：
(1)首先要有一个或多个表空间存放归档表，可以加上配额，使用原有的表空间在技术上是可行的，但 Oracle 建议它们与常规的数据分开存放。
(2)先要建立一个归档名，作为一个数据库的对象，可以为它指定 default 属性，含义是把所有要归档的表都建立在该缺省的归档名下。
(3)可以根据需要建立多个闪回归档，这取决于你需要多少种不同的保留时间。可以调整保留时间，一旦超过了保留期限，后台进程 FDBA 将自动删除该表记录，也可以在保留期内手动进行删除操作。
(4)关于两个权限：
授予用户创建，修改或删除闪回回档，代码如下：
//授予用户创建，修改或删除闪回回档
flashback archive administer --授予用户对表进行归档
flashback archive
(5)启用表的闪回归档需要使用 alter table 表 flashback archive 归档名
(6)不能对已经定义为闪回归档的表再使用 drop 或 truncate 等 DDL 命令。
(7)闪回归档有前提条件，undo_management 要设为 on， 否则修改表时报
ORA_55614 错误
(8)关于闪回归档的视图：
DBA_FLASHBACK_ARCHIVE //描述配置的归档
DBA_FLASHBACK_ARCHIVE_TABLES //查看归档的表
DBA_FLASHBACK_ARCHIVE_TS //列出使用的表空间
【例13-11】使用sys 用户建一个表空间fda，然后在该表空间上建立归档fla1
可以保存1年数据。建立表emp1,把该表和归档建立关系后，查看是否可以做做DML操作
建立表空间、归档具体代码如下：
create tablespace fda datafile ‘/u01/oradata/timran11g/fda01.dbf’ size 5m;

create flashback archive fla1 tablespace fda quota 2m retention 1 year;

grant flashback archive on fla1 to scott;

建emp1表,具体代码如下：
–scott用户下，建立表 EMP1

create table EMP1
(
empno VARCHAR2(30)，
job VARCHAR2(30)，
sal NUMBER(6，2)
);
insert into emp1 (empno，job，sal) values(9，‘worker’，3000);
insert into emp1 (empno，job，sal) values(8，‘hunter’，100);
Commit;

–把表emp1定义为闪回归档
alter table emp1 flashback archive fla1;

可以做些 DML 操作。在使用闪回查询(as of)验证，发现做一些 DDL 操作会报错。

– 做DDL 操作

alter table emp1 drop column empno ;
truncate table emp1;
drop table emp1;

–会报ORA-55610错误: 针对历史记录跟踪表的 DDL 语句无效.

–取消归档保护

–sys用户下查看有哪些归档名
select * from dba_flashback_archive
–查看归档使用表空间的信息
select * from dba_flashback_archive_ts;
—查看表 emp1 和归档名的关系
select * from dba_flashback_archive_tables;

–sys用户下，将表 scott.emp1 从闪回归档中取消，
alter table scott.emp1 no flashback archive;
13.5.4闪回版本查询
闪回查询仅仅能够得到过去某个时间点上的数据，但是无法反映出一段时间内数据
表中数据变化的细节。Oracle 10g 的闪回版本查询可以对时间段内数据表的每行变化(不同版本)进行查询。
格式如下：
select … from … versions between
其中，select 后面可以选择伪列，来获得事务的开始、结束时间、SCN 号、ID 号等。
【例13-11】建立表t3，插入测试记录。对该表进行更新、删除数据操作后，通过闪回版本查询，查询表发生的变化。
创建表插入测试记录，代码如下：
–第十三章\sjkbf.sql

create table t3 (id int， name char(10));
insert into t3 values(1，‘tim’);
insert into t3 values(2，‘mike’);
insert into t3 values(3，‘brain’);
insert into t3 values(4，‘cade’);
commit;

更新新表数据，代码如下：
update t3 set name=‘nelson’ where id=4;
commit;
delete t3 where id=2;
commit;
update t3 set id=id+100;
commit;
看看 t3 表经历的时间变化，代码如下：
select versions_startscn， versions_endscn， versions_xid，
versions_operation，id，name from scott.t3 versions between scn minvalue and
maxvalue;
执行后如图13-28所示。

图13-28查询表经历的变化

看看 t3 表经历的操作，代码如下：

select versions_xid， versions_operation，id， name from t3 versions between scn
minvalue and maxvalue;

执行后如图13-29所示。

图13-29 查询表经历的变化

使用闪回版本查询总结：
闪回版本查询不能用于外部表、临时表或 V$视图。原因是这些对象都不生成撤销数据。(临时表的撤销是基于session 的)。
闪回版本中的内容不包括未提交的 DML 语句。
13.5.5闪回事务查询
闪回事务查询可以提供撤销查询语句。Flashback Transaction Query 是一个诊断恢复工具，可以用于在事务级识别数据库的变更，与Flashback Versions Query类似， Flashback Transaction Query允许获取两个特定时间点之间的所有变更。Flashback Transaction Query 允许对表进行基于事务的恢复。
从 flashback_transaction_query 这个视图查询引起数据变化的事务，和撤销事务的 SQL 语句。也就是查询 operation 和 undo_sql 列，可以和闪回版本查询结合起来使用。

【例13-12】使用例13-11的表t3，查询出该表数据。通过事务号，查询出flashback_transaction_query视图中undo_sql列对应的数据，对表t3进行会恢复。

查询t3表数据，代码如下：
–第十三章\sjkbf.sql

select * from t3
执行后如图13-30所示。

图13-30 恢复到表最初的状态

需要启用Supplemental logging，代码如下：
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
在sys用户下，通过事务号，查询flashback_transaction_query视图，代码如下：
select xid，undo_sql from flashback_transaction_query where undo_sql is not null and xid=hextoraw(‘0A000F0035270000’);
执行后如图13-31所示。

图13-31查询ud_sql列数据

把undo_sql 列数据复制出来，代码如下：
update “SYS”.“T3” set “ID” = ‘4’ where ROWID = ‘AAATd3AABAAAaAhAAD’;

update “SYS”.“T3” set “ID” = ‘3’ where ROWID = ‘AAATd3AABAAAaAhAAC’;

update “SYS”.“T3” set “ID” = ‘1’ where ROWID = ‘AAATd3AABAAAaAhAAA’;

Commit；

执行后如图13-32所示。

图13-32 恢复到表最初的状态

从查询结果上可以看出，表t3的更新操纵都撤销了。
闪回事务查询使用总结：
需要启用Enable Supplemental Logging ，并且数据库版本 10.0 compatibility。
查询 flashback_transaction_query 视图需要 有SELECT ANY TRANSACTION 权限。