数据恢复相关sql

1、SCN和时间转换sql

--时间转为scn
SELECT TIMESTAMP_TO_SCN(to_date('2018-01-12 09:27:00','yyyy-mm-dd hh24:mi:ss')) FROM DUAL;
--当前时间的scn
SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
--scn转为时间
SELECT TO_CHAR(SCN_TO_TIMESTAMP(23490327),'YYYY-MM-DD hh24:mi:ss') FROM DUAL;
--scn与时间对应字典
SELECT * FROM SYS.SMON_SCN_TIME

2、根据scn查询被删的数据

SELECT * FROM TABLE AS OF SCN 23489216 ORDER BY id DESC;

3、根据时间查询被删除的数据
SELECT *
  FROM TABLE AS OF TIMESTAMP TO_TIMESTAMP('2017-11-08 9:00:00', 'yyyy-mm-dd hh24:mi:ss')
 ORDER BY ID DESC;


4、将被删的数据插入原来的表
INSERT INTO TABLE 
  SELECT *
    FROM TABLE AS OF TIMESTAMP TO_TIMESTAMP('2017-11-08 9:00:00', 'yyyy-mm-dd hh24:mi:ss')
   WHERE ID = 673722251850302;

--查一分钟前的数据
SELECT * FROM scott.emp AS OF TIMESTAMP SYSDATE-1/1440;