ORACLE误删恢复数据

– 1. 如果误删了某条数据，可以通过闪回查询查询改某个时间点的数据。
select *
from TABLE_NAME
as of timestamp to_timestamp(‘2023-04-28 16:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
where ID=‘xxxxxx’(删除时的条件);

– 确认数据无误，插入原来误删的数据
insert into TABLE_NAME
select *
from TABLE_NAME
as of timestamp to_timestamp(‘2023-04-28 16:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
where ID=‘xxxxxx’(删除时的条件);

– 2. 查看某个时间点数据前后变动记录
select COLUMN_NAME,
versions_starttime, versions_endtime, versions_operation
from mgj_commission_sqlgroups versions
between timestamp to_timestamp(‘2023-09-07 10:00:00’,‘yyyy-mm-dd hh24:mi:ss’) and to_timestamp(‘2023-09-07 11:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
where YOUR_CONDITION
order by versions_starttime desc;

– 2.1 查询最大时间区间内的修改
– 该查询很花时间
select COLUMN,
versions_starttime, versions_endtime, versions_operation
from TABLE_NAME versions
between timestamp minvalue and maxvalue
where versions_starttime is not null
order by versions_starttime desc;

– 3. 整个表进行闪回
alter table TABLE_NAME enable row movement;
flashback table TABLE_NAME to timestamp to_timestamp(‘2023-11-15 16:00:00’,‘yyyy-mm-dd HH24:MI:SS’);