ORACLE表数据误删除的恢复方法(提交事务也可以)
缓存加时间戳
开启行移动功能:ALTER TABLE tablename ENABLE row movement
把表还原到指定时间点:flashback table tablename to timestamp to_timestamp(''2011-02-28 10:40:00'',''yyyy-mm-dd hh24:mi:ss'');
关闭行移动功能: alter table tablename disable row movement
快照加时间戳
方式1: select * from tablename AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '100' MINUTE)
方式2: select * from tablename as of timestamp to_timestamp(2011-05-21 11:40:00','YYYY-MM-DD HH24:MI:SS');
例如恢复1分钟前的数据:create table newtablename as (select * from tablename AS OF TIMESTAMP (SYSTIMESTAMP - 1/1440));
闪回被drop的表
查询回收站: select table_name,dropped from user_tables
记得表名: flashback table tablename to before drop
不记得表名: flashback table "Bin$DSbdfd4rdfdfdfegdfsf==$0" to before drop rename to tablename
闪回数据库
关闭数据库: shutdown immediate;
启到MOUNT状态: startup mount;
开启归档: alter database archivelog
开启闪回功能: alter database flashback on
闪回数据库到闪回SCN(递增的数字,有四种SCN:系统检查的,文件检查的,启动S,终止)点: flashback database to scn SCNNO;
基于时间戳闪回: flashback database to timestamp to_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');
启动数据库: alter database open;
详见博客:
https://blog.csdn.net/jiajane/article/details/49280277
https://www.cnblogs.com/hqbhonker/p/3977200.html