Oracle 闪回查询

--闪回查询

--设置时间格式
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

--SCN转换成时间：
select to_char(scn_to_timestamp(1795801),'YYYY-MM-DD HH24:MI:SS') from dual;
--时间转换成SCN：
col scn for 9999999999999999999999
select timestamp_to_scn(to_date('2015-10-25 09:10:23','YYYY-MM-DD HH24:MI:SS')) scn from dual;

--11g
--闪回时间
select /*+parallel(tb1,8)*/ * from tb1 as of timestamp to_timestamp('2015-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss');
--闪回SCN
select /*+parallel(tb1,8)*/ * from tb1 as of scn 2032782;
--闪回查询减去现有数据
select /*+parallel(tb1,8)*/ * from tb1 as of timestamp to_timestamp('2015-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss')
minus
select /*+parallel(tb1,8)*/ * from tb1;

--插入数据至临时表(根据时间)
CREATE TABLE LIS.ZXQ_TMP AS SELECT /*+parallel(E_T_CHGRANK,8)*/ * FROM lis.E_T_CHGRANK AS OF TIMESTAMP TO_TIMESTAMP('2018-10-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
--插入数据至临时表(根据SCN)
CREATE TABLE LIS.ZXQ_TMP  AS SELECT /*+parallel(E_T_CHGRANK,8)*/ * FROM lis.E_T_CHGRANK as of scn 2032782;

--只要部分字段，需要创建临时表
--创建临时表
CREATE TABLE AML.HXT
(
  NOTYPE  VARCHAR2(25) NOT NULL,
  NOLIMIT VARCHAR2(12) NOT NULL,
  MAXNO   INTEGER NOT NULL
)
TABLESPACE ZBJ;  
--插入数据至临时表(根据时间)
INSERT INTO AML.HXT SELECT * FROM AML.LDMAXNO AS OF TIMESTAMP TO_TIMESTAMP('2017-02-10 16:45:00', 'YYYY-MM-DD HH24:MI:SS');
COMMIT;
--插入数据至临时表(根据SNC)
INSERT INTO TESTTABLE2 SELECT * FROM TESTTABLE2 AS OF SCN 123456 WHERE DEPTNO='11';

 

--error
--闪回查询（根据时间）
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
--开启表tb_emp表的row movement功能
alter table tb_emp enable row movement;