数据闪回

# 一、 误delete恢复，truncate无法恢复
1. 备份

```sql
create table pat_master_indexbf  as
SELECT * FROM pat_master_index AS OF TIMESTAMP TO_TIMESTAMP('20180125 12:02:00','YYYYMMDD HH24:MI:SS');
```
2. 将执行修改后表中新增的数据备份一次

```sql
create table pat_master_index_xin
as
select * from pat_master_index  t where t.inp_no <>'170514970';
```

3. 检查查询修改之前的数据是否正确

```sql
SELECT * FROM pat_master_index AS OF TIMESTAMP TO_TIMESTAMP('20180125 12:02:00','YYYYMMDD HH24:MI:SS');
```

4. 启用表的行移动功能，执行回滚语句

```sql
alter table pat_master_index enable row movement;
flashback table pat_master_index to timestamp  to_timestamp('20180125 12:02:00','YYYYMMDD HH24:MI:SS');
```
5. 关闭表的行移动功能

```sql
alter table pat_master_index disable row movement;
```
6. 将回滚后的表与备份的做对比，再将对比出的数据添加到pat_master_index中

```sql
insert into pat_master_index select a.* from pat_master_index_xin a left join pat_master_index b
on a.patient_id=b.patient_id  where b.patient_id is null;
select a.* from pat_visit a left join pat_master_index b  on a.patient_id=b.patient_id
where b.patient_id is null;
select * from pat_visit t where t.patient_id in  (select a.patient_id from pat_master_index_xin a left join pat_master_index b   on a.patient_id=b.patient_id
where b.patient_id is null);
```

# 二、误drop表
使用闪回查询，数据库须开启automatic undo management,须有undo表空间，须设置好回滚段的保留时间
```sql
-- 在sqlplus中查看undo_management参数值是否为AUTO，如果是“MANUAL”手动，需要修改为“AUTO”;
show parameter undo   --查询闪回参数设置
alter system set undo_management='AUTO' scope=spfile;
-- 查看回滚段的保留时间undo_retention,默认900秒（15分钟）
-- 闪回表
 --1. drop table  test_1234
 --2.删除test_1234表后，该表的block还在表空间中，查询回收站可以看到被删除的对象
select * from dba_recyclebin;
 --3.闪回表到删除之前：
 flashback table test_1234 to before drop;
```

# 三，查询误删除表
v$active_session_history，dba_hist_active_sess_history
语句1：在线ASH不存在则查询下个表（v$sqlarea根据SQL_ID查询语句）
```sql
select * from v$active_session_history T  where  t.SAMPLE_TIME between  to_date('20211228 10:08:20','yyyymmdd hh24:mi:ss') and
       to_date('20211228 10:08:30','yyyymmdd hh24:mi:ss') order by SAMPLE_TIME desc
```
语句2：
```sql
select * from dba_hist_active_sess_history T where  t.SAMPLE_TIME between  to_date('20211228 10:08:20','yyyymmdd hh24:mi:ss') and
       to_date('20211228 10:08:30','yyyymmdd hh24:mi:ss') order by SAMPLE_TIME desc
```