Oracle闪回查询

---闪回查询

SQL> select * from emp as of timestamp (systimestamp - interval '2' minute); ---查询emp表2分钟之前的数据

SQL> select * from emp as of timestamp (systimestamp - interval '120' second); ---查询emp表120秒之前的数据

SQL> select * from emp as of timestamp (systimestamp - interval '12' hour); ---查询emp表12小时之前的数据

SQL> select * from emp as of timestamp (systimestamp - interval '12' day); ---查询emp表12天之前的数据

SQL> select * from emp as of timestamp sysdate-2; ---查询emp表2天之前的数据

SQL> select * from table_name as of timestamp to_timestamp('2023-05-22 10:00:00','yyyy-mm-dd hh24:mi:ss'); ---根据时间查询

时间和scn之间的转换

SQL> select timestamp_to_scn(to_timestamp('2023-08-04 06:15:22','yyyy-mm-dd hh24:mi:ss')) scn from dual;

SCN

----------

2280824

SQL> select scn_to_timestamp(2280824) scn from dual;

SCN

---------------------------------------------------------------------------

04-AUG-23 06.15.21.000000000 AM SQL>

闪回操作

闪回操作
SQL> create table t1  as select  *  from  emp ;

Table created.

SQL> 

SQL> select  count(*)  from  t1 ;

  COUNT(*)
----------
        14

SQL> 

SQL> delete from t1 ; 

14 rows deleted.

SQL> commit ;

Commit complete.

SQL> 


SQL> select  count(*)  from  t1 ;

  COUNT(*)
----------
         0

SQL> 


SQL> select * from t1 as of timestamp (systimestamp - interval '2' minute);

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600
       300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                1250
       500         30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81                2975
                   20

      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250
      1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81                2850
                   30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81                2450
                   10

      7788 SCOTT      ANALYST         7566 19-APR-87                3000
                   20

      7839 KING       PRESIDENT            17-NOV-81                5000
                   10


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500
         0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                1100
                   20

      7900 JAMES      CLERK           7698 03-DEC-81                 950
                   30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81                3000
                   20

      7934 MILLER     CLERK           7782 23-JAN-82                1300
                   10


14 rows selected.

SQL> 
SQL> 


SQL>  select count(*)  from t1 as of timestamp (systimestamp - interval '2' minute);

  COUNT(*)
----------
        14

SQL> 
SQL>


SQL> ALTER TABLE t1 ENABLE ROW MOVEMENT;

Table altered.

SQL> flashback table t1 to timestamp (systimestamp - interval '2' minute);

Flashback complete.

SQL>  select * from t1;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600
       300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                1250
       500         30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81                2975
                   20

      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250
      1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81                2850
                   30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81                2450
                   10

      7788 SCOTT      ANALYST         7566 19-APR-87                3000
                   20

      7839 KING       PRESIDENT            17-NOV-81                5000
                   10


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500
         0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                1100
                   20

      7900 JAMES      CLERK           7698 03-DEC-81                 950
                   30


     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81                3000
                   20

      7934 MILLER     CLERK           7782 23-JAN-82                1300
                   10


14 rows selected.

SQL>