---闪回查询
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>
标签：闪回,20,timestamp,查询,----------,SQL,Oracle,81,select
From： https://www.cnblogs.com/william2019/p/17605584.html