利用Flashback轻松找回误删除数据

最新推荐文章于 2024-04-16 10:43:57 发布

最新推荐文章于 2024-04-16 10:43:57 发布 · 618 阅读

文章标签：

#数据库

本文详细介绍了SQL数据库中创建表、插入数据、移动行、删除表、在回收站查询表，并通过FlashbackDrop功能进行数据恢复的过程。阐述了如何在不依赖备份的情况下，将已删除的数据恢复回来。

测试：

1.首先创建一张表TEST

create table test (name int)

2.插入两条数据：

insert into test values (1);

3.对表启动移动行

ALTERTABLE table ENABLE ROW MOVEMENT;

4.删除表：

drop table test

5.在回收站查询表：

SHOWRECYCLEBIN;

或者

SELECTobject_name AS recycle_name, original_name, type

FROMrecyclebin;

这个时候我们在回收站还是可以看到表test的，Flashback Drop就是从这里讲数据恢复的。

6.恢复数据：

FLASHBACK table to test to before drop;

7.查询数据：

select * from test

这时候就可以看到数据已经都还原了。非常好的功能，不需要像SQL Server那样从备份中还原。

Flashbackup drop有一些条件限制：

Objects must meet the following prerequisites to be eligible for retrieval from the
recycle bin:
■ The recycle bin is only available for non-system, locally managed tablespaces. If a
table is in a non-system, locally managed tablespace, but one or more of its
dependent segments (objects) is in a dictionary-managed tablespace, then these
objects are protected by the recycle bin.
■ Tables that have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD)
policies defined over them are not protected by the recycle bin.
■ Partitioned index-organized tables are not protected by the recycle bin.
■ The table must not have been purged, either by a user or by Oracle Database
because of a space reclamation operation.