Oracle闪回drop和闪回查询

简介:         Oracle提供了7种闪回技术,分别为:闪回drop、闪回查询、闪回数据归档、闪回表、闪回版本查询、闪回事务、闪回数据库。此次验证下Oracle数据库闪回drop和闪回查询。 一、闪回DROP         又名闪回删除。

        Oracle提供了7种闪回技术,分别为:闪回drop、闪回查询、闪回数据归档、闪回表、闪回版本查询、闪回事务、闪回数据库。此次验证下Oracle数据库闪回drop和闪回查询。

一、闪回DROP

        又名闪回删除。

1、理解回收站(recyclebin)

        从管理的角度为每个用户“分配”一个回收站,但这个回收站并不实际开辟空间(只是个逻辑容器),当drop table时(非purge),原来的表所使用的段中的数据并没有真正的删除。实际上是把table的段名以回收站方式重命名。该段所在表空间不足需要扩展时,回收站中的信息会被自动清除。(先清除后扩展)
image

示例:
SQL> show parameter recyclebin   
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON    

当初始化参数recyclebin为on时,每个用户都有了自己的回收站(延迟参数,session下次连接有效)
如果参数设为off 就取消了用户的回收站,那么当你drop table就相当于purge了。

SQL> create tablespace test datafile '/u01/oradata/prod/test01.dbf' size 1m;
SQL> create table scott.t1(id int) tablespace test;
SQL> select segment_name from dba_segments where tablespace_name='TEST';   查看test表空间下有了一个段
SEGMENT_NAME
T1
SQL> select sum(bytes) from dba_free_space where tablespace_name='TEST';   看这个段有多少空闲空间

SUM(BYTES)
----------
    917504

SQL> insert into scott.t1 values(1);
SQL> insert into scott.t1 select * from scott.t1;   将表空间撑满
/
/
第 1 行出现错误:
ORA-01653: 表 SCOTT.T1 无法通过string (在表空间 TEST 中) 扩展
SQL> select count(*) from scott.t1;
  COUNT(*)
----------
     65536
SQL> select sum(bytes) from dba_free_space where tablespace_name='TEST';   没有空闲空间
SQL> drop table scott.t1;
SQL> select segment_name from dba_segments where tablespace_name='TEST';
SEGMENT_NAME
---------------------------------------------------------------------------------
BIN$4KZBTYTKocDgQAB/AQAKRA==$0

SQL> select sum(bytes) from dba_free_space where tablespace_name='TEST';
SUM(BYTES)
----------
    983040

        请看,TEST表空间中的空闲空间又回来了,这说明如果test表空间不够时,这部分空闲空间是可以被重新利用的,实际上即使你设置了表空间autoextend特性,Oracle 会先使用recyclebin,若空间还不够,再考虑autoextend.

SQL> create table scott.emp1 tablespace test as select * from scott.emp;   挤占test表空间
SQL> select sum(bytes) from dba_free_space where tablespace_name='TEST';
SUM(BYTES)
----------
    917504
SQL> select segment_name from dba_segments where tablespace_name='TEST';  
SEGMENT_NAME
---------------------------------------------------------------------------------
EMP1

t1表的数据已经被冲掉了,使用闪回删除无法找回了。

2、关于回收站中的对象的闪回和清除

闪回和清除的顺序不同
闪回使用LIFO (后进先出)
清除使用FIFO (先进先出)
假设回收站里有两个t1表,看以下两条语句:

SQL> flashback table t1 to before drop;   闪回的是最新的那个t1表。
SQL> purge table t1;   清除的是最旧的那个t1表。

如果想避免混淆,可以直接给出回收站里的表名

SQL> flashback table " BIN$qrJLbL74ZgvgQKjA8Agb/A==$0" to befroe drop;  (注意双引号)
SQL> purge table "BIN$qrJLbL74ZgvgQKjA8Agb/A==$0";

SQL> purge recyclebin;   清空回收站

3、如何恢复同一个schema下准备闪回的表已有同名的对象存在,闪回drop需要重命名.

SQL> flashback table t1 to before drop rename to test_old;

4、system 表空间的对象没有回收站,所以在sys下缺省使用system表空间时,drop table会直接删除对象

5、如果一个表上面有索引和约束,drop后再闪回表,索引和约束还在吗?

create table t (id int,name char(10));
alter table t add constraint pk_t primary key(id);
insert into t values (1,'test1');
insert into t values (2,'test2');
commit;
SQL> select * from t;
        ID NAME
---------- ----------
         1 test1
         2 test2
看一眼约束和索引
SQL> select * from user_indexes;
SQL> select * from user_constraints;

SQL> drop table t;   表被drop到回收站,再看一眼约束和索引

SQL> select * from user_indexes;   索引不见了
SQL> select * from user_constraints;   约束有,但乱码(除外键约束外)

SQL> flashback table t to before drop;

再看约束和索引
SQL> select * from user_indexes;   索引回来了,但乱码
SQL> select * from user_constraints;   约束也在,但乱码

分别重命名索引和约束
SQL> alter index "BIN$yF3hbvIbioTgQAB/AQAJlg==$0" rename to pk_t;
SQL> alter table t rename constraint "BIN$yF3hbvIaioTgQAB/AQAJlg==$0" to pk_t;

6、查看闪回区内容

select OBJECT_NAME,ORIGINAL_NAME,OPERATION,CREATETIME,DROPTIME from user_recyclebin;
select * from tab;

二、闪回查询

flashback query:(用于DML 误操作并且commit)

1、要点:

利用在undo tablespace 里已经提交的undo block(未被覆盖),可以查询表的过去某个时间点的数据。
通过设置undo_retention参数设置前镜像的保留时间。
查询的语法:
select … as of scn | timestamp

2、undo_retention

可以通过show parameter undo_retention查看该参数的值,默认900,单位是秒。关于undo_retention的介绍可以查看lian链接:link

3、示例:

sys:
create table scott.student (sno int,sname char(10),sage int);
insert into scott.student values(1,'tt1',21);
insert into scott.student values(2,'tt2',22);
insert into scott.student values(3,'tt3',23);
insert into scott.student values(4,'tt4',24);
commit;

SQL> select * from scott.student;

SQL> select current_scn from v$database;   取scn 1或者查询当前时间
SQL> delete scott.student where sno=1;
SQL> commit;
SQL> select * from scott.student;

SQL> select current_scn from v$database;   取scn 2
SQL> update scott.student set sage=50;
SQL> commit;
SQL> select * from scott.student;

SQL>select * from scott.student as of scn scn1;   闪回查询到scn1;
SQL>select * from scott.student as of scn scn2;   闪回查询到scn2;

SQL>select * from scott.student as of timestamp to_timestamp('2019-03-18 11:31:01','YYYY-MM-DD HH24:MI:SS');  //闪回查询基于时间

4、查看 DELETE 及 UPDATE 操作修改的数据:

SQL> SELECT *
FROM tab AS OF TIMESTAMP  to_timestamp('time_point', 'yyyy-mm-dd hh24:mi:ss')
MINUS
SELECT *
FROM tab;
其中将查询得 tab 表在 time_point 时点之后因 DELETE 及 UPDATE 操作修改的数据。

5、查看 INSERT 操作修改的数据:

SQL> SELECT *
FROM tab
MINUS
SELECT *
FROM tab AS OF TIMESTAMP  to_timestamp('time_point', 'yyyy-mm-dd hh24:mi:ss');
其中将查询得 tab 表在 time_point 时点之后因 INSERT 操作修改的数据。

参考文章
1、http://www.cnblogs.com/autopenguin/p/5952671.html

相关文章
|
7月前
|
Oracle 关系型数据库 Linux
【YashanDB 知识库】通过 dblink 查询 Oracle 数据时报 YAS-07301 异常
客户在使用 YashanDB 通过 yasql 查询 Oracle 数据时,遇到 `YAS-07301 external module timeout` 异常,导致 dblink 功能无法正常使用,影响所有 YashanDB 版本。原因是操作系统资源紧张,无法 fork 新子进程。解决方法包括释放内存、停掉不必要的进程或增大进程数上限。分析发现异常源于 system() 函数调用失败,返回 -1,通常是因为 fork() 失败。未来 YashanDB 将优化日志信息以更好地诊断类似问题。
|
6月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的闪回版本查询
本文介绍了Oracle数据库的闪回版本查询(Flashback Version Query)功能,通过示例详细讲解了其使用方法。闪回版本查询可获取指定时间区间内行的不同版本,利用`versions between`子句实现。文中包含视频讲解,并通过创建测试表、插入数据及执行查询等步骤,演示如何获取历史版本信息和伪列详情,帮助用户深入了解该功能的实际应用。
129 13
|
5月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回数据库
Oracle闪回数据库功能类似于“倒带按钮”,可快速将数据库恢复至 earlier 状态,无需还原备份。本文介绍了闪回数据库的使用方法及实战案例:包括设置归档模式、开启闪回功能、记录SCN号、执行误操作后的恢复步骤等。通过具体 SQL 操作演示了如何利用闪回数据库恢复被误删的用户数据。注意,使用此功能前需确保数据库为归档模式。
164 9
|
5月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回数据归档
本文介绍了Oracle闪回数据归档(Flashback Data Archive)的功能与实现方法。闪回数据归档可将表中的历史数据进行归档,支持全面的历史数据查询,引入了“Oracle Total Recall”概念。文中通过具体实战步骤演示了如何创建和启用闪回数据归档:包括创建表空间、数据归档,设置默认归档,授予用户权限,开启表的归档功能,并展示了误操作后如何通过归档恢复数据。最后通过执行计划验证了数据来源于归档。
140 4
|
5月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回事务查询
Oracle数据库的闪回事务查询(Flashback Transaction Query)是闪回版本查询的扩充,可用于审计或撤销已提交的事务。通过`flashback_transaction_query`视图,可生成还原特定事务的SQL语句。本文介绍了其基本概念,并通过实战演示如何使用该功能:从授权、开启UNDO数据增强,到创建测试表和事务,最后利用闪回查询撤销已提交的事务,验证数据恢复效果。附带视频讲解,帮助深入理解。
139 3
|
5月前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle的闪回删除
本文介绍了Oracle数据库的闪回删除(Flashback Drop)功能,该功能可从回收站恢复已删除的对象至删除前状态。文章详细讲解了回收站的工作原理及操作步骤,包括检查回收站功能是否开启、删除表、查看回收站内容以及使用闪回删除恢复数据等实战操作。通过具体示例,演示了如何恢复被删除的员工表及其数据,并处理同名表冲突问题。文末还附有视频讲解,帮助读者更直观地理解操作流程。
104 1
|
6月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle数据库的闪回查询
本文介绍了Oracle数据库的闪回查询(Flashback Query)功能及其实际应用。闪回查询通过`AS OF`子句,结合时间戳或SCN号,可查询历史数据状态,帮助分析数据差异。文中通过具体示例演示了如何使用闪回查询:创建测试表、记录当前SCN号、更新数据并提交事务,最后通过闪回查询获取历史数据。附带的视频和代码块详细展示了操作步骤与结果。
235 4
|
6月前
|
Oracle 关系型数据库 Linux
【YashanDB知识库】通过dblink查询Oracle数据时报YAS-07301异常
【YashanDB知识库】通过dblink查询Oracle数据时报YAS-07301异常
|
6月前
|
Oracle 关系型数据库 MySQL
【YashanDB知识库】oracle dblink varchar类型查询报错记录
这篇文章主要介绍了 Oracle DBLINK 查询崖山 DB 报错的相关内容,包括 ODBC 安装配置、数据源配置、dblink 环境配置、问题原因分析及规避方法。问题原因是 dblink 连接其他数据库时 varchar 类型转换导致的,还介绍了 long 类型限制、char 等类型区别,规避方法是修改参数 MAX_STRING_SIZE 支持 32K。
|
7月前
|
Oracle 关系型数据库 Linux
【YashanDB 知识库】通过 dblink 查询 Oracle 数据时报 YAS-07301 异常
某客户在使用 YashanDB 通过 yasql 查询 Oracle 数据时,遇到 `YAS-07301 external module timeout` 异常,导致 dblink 功能无法正常使用,影响所有版本。问题源于操作系统资源紧张,无法 fork 新子进程。解决方法包括释放内存、停掉不必要的进程或增大进程数上限。分析发现异常原因为系统调用 fork() 失败。经验总结:优化日志记录,提供更多异常信息。