再也不用担心删库跑路了!!
一:根据时间来恢复:
1、查询数据库当前时间(目的是为了检查数据库时间是否与你电脑时间相近,避免时间不同而将数据恢复到错误时间点)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
2、查询删除数据时间点之前的数据select * from tm_user as of timestamp to_timestamp('2018-10-18 15:30:11','yyyy-mm-dd hh24:mi:ss') where real_name='杨继雷' ;
--update mp_user m set m.subscribe_time=(select t.subscribe_time from yy t where t.openid=m.openid) where m.openid in (select t.openid from yy t);
--select count(1) from mp_user;
--select openid from mp_user group by openid having count(openid)>1
--create table yy (subscribe_time date,openid varchar2(50))
--select count(*) from yy for update;
--drop table yy;(若没有数据 ,将时间继续提前)
3、恢复数据(激动人心的时刻)
flashback table tm_user to timestamp to_timestamp('2018-10-18 15:30:11','yyyy-mm-dd hh24:mi:ss');
大功告成,数据恢复成功;
但是也可能会出现问题,比如报错:ORA-08189:未启用行移动功能,不能闪回表; cannot flashback the table because row movement is not enabled
不要怕,这个很简单;
alter table tm_user enable row movement;
然后再次执行上面SQL即可;
二:根据数据库SCN恢复数据
1、查询当前数据库SCN号
select current_scn from v$database;(不能执行的话,切换到sys用户或system用户查询)
查询到的当前值为:91799986
2、缩小SCN号查询被删除表数据(若无数据继续缩小SCN,由于数据库操作不止一人,SCN号变化比较多,可以多缩小几个号)
select * from tm_user as of scn 4368581234 where real_name='杨继雷' order by ts desc;
3、恢复数据
flashback table tm_user to scn 4368581234;
如果报 未启用行移动功能: cannot flashback the table because row movement is not enabled
可以使用上面方法,也可以使用下面这种:
首先查询删除表的所有字段:select wm_concat(column_name)from user_tab_cols where table_name='TM_USER'
然后执行:insert into tm_user (PK_USER,PK_ORG,USER_NAME,PASSWORD,LANGUAGE,USER_TYPE,USER_CHANNEL,REAL_NAME,GENDER,USER_PHONE,EMAIL,BIRTHDAY,ADDRESS,ZIP_CODE,STATUS,LAST_LOGIN_TIME,AVATAR,ATTENDANCE_CODE,TS,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE,CUSERID,FK_USER,IS_WX,PK_PSNDOC,PK_GROUP,PK_FINANCE,PK_DEPARTMENT)
select PK_USER,PK_ORG,USER_NAME,PASSWORD,LANGUAGE,USER_TYPE,USER_CHANNEL,REAL_NAME,GENDER,USER_PHONE,EMAIL,BIRTHDAY,ADDRESS,ZIP_CODE,STATUS,LAST_LOGIN_TIME,AVATAR,ATTENDANCE_CODE,TS,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE,CUSERID,FK_USER,IS_WX,PK_PSNDOC,PK_GROUP,PK_FINANCE,PK_DEPARTMENT
from tm_user as of scn 4368581234 where real_name='杨继雷' order by ts desc;
select * from tm_user tu where tu.real_name='杨继雷'