定义:简单来说,就是能在一定的条件下,把误删的数据找回来
下面是一些基本语法
查询过去某一个时刻的状态
例如: 当前scott.emp表中scott的工资
SQL> select ename ,sal from scott.emp;
ENAME SAL
---------- ----------
SMITH 800
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
ENAME SAL
---------- ----------
SCOTT 3000 这是scott的工资
KING 5000
FORD 3000
update scott.emp set sal=0
where ename ='SCOTT';
commit;
select ename ,sal from scott.emp
where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
我要查处scott原来的工资多少,然后恢复原来的工资
查处过去时刻的工资
问题:过去时刻怎么表示 如何表示过去的某个时间
1分钟前 sysdate -1/1440
5分钟前 sysdate -5/1440
1小时前 sysdate -1/24
1天前 sysdate -1
查询过去某个时刻的语法:
select 字段名,.....
from 表名
AS OF TIMESTAMP <T1> 或者 scn
where 条件
求1分钟前scott的工资
select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-5/1440)
where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-10/1440)
where ename ='SCOTT';
2
ENAME SAL
---------- ----------
SCOTT 3000
具体的过去的某个时间
select ename ,sal from scott.emp as of timestamp to_timestamp('2014-9-15 17:00:00','yyyy-mm-dd hh24:mi:ss')
where ename ='SCOTT';
闪回查询不能跨越DDL语句
恢复
update scott.emp
set sal=(select sal from scott.emp as of timestamp(sysdate-10/1440)
where ename ='SCOTT')
where ename ='SCOTT';
commit;
SQL> select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
扩展题1:
delete from scott.emp where rownum <=4;
commit;
原来14条,现在10条,查处到底哪4条数据被删除了
原有的-现在的 --->查处你被删除
select * from scott.emp as of timestamp(sysdate -5/1440)
minus
select * from scott.emp ;
还原;
insert into scott.emp
(
select * from scott.emp as of timestamp(sysdate -5/1440)
minus
select * from scott.emp )
扩展题2: 可以建立一视图vemp_15,可以看scott.emp表15分钟的数据
create or replace view vemp_15
as
select * from scott.emp as of timestamp(sysdate -15/1440);
(还可以基于scn号,时间段来查,其他的听下回分解 )
下面是一些基本语法
查询过去某一个时刻的状态
例如: 当前scott.emp表中scott的工资
SQL> select ename ,sal from scott.emp;
ENAME SAL
---------- ----------
SMITH 800
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
ENAME SAL
---------- ----------
SCOTT 3000 这是scott的工资
KING 5000
FORD 3000
update scott.emp set sal=0
where ename ='SCOTT';
commit;
select ename ,sal from scott.emp
where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
我要查处scott原来的工资多少,然后恢复原来的工资
查处过去时刻的工资
问题:过去时刻怎么表示 如何表示过去的某个时间
1分钟前 sysdate -1/1440
5分钟前 sysdate -5/1440
1小时前 sysdate -1/24
1天前 sysdate -1
查询过去某个时刻的语法:
select 字段名,.....
from 表名
AS OF TIMESTAMP <T1> 或者 scn
where 条件
求1分钟前scott的工资
select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-5/1440)
where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-10/1440)
where ename ='SCOTT';
2
ENAME SAL
---------- ----------
SCOTT 3000
具体的过去的某个时间
select ename ,sal from scott.emp as of timestamp to_timestamp('2014-9-15 17:00:00','yyyy-mm-dd hh24:mi:ss')
where ename ='SCOTT';
闪回查询不能跨越DDL语句
恢复
update scott.emp
set sal=(select sal from scott.emp as of timestamp(sysdate-10/1440)
where ename ='SCOTT')
where ename ='SCOTT';
commit;
SQL> select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
扩展题1:
delete from scott.emp where rownum <=4;
commit;
原来14条,现在10条,查处到底哪4条数据被删除了
原有的-现在的 --->查处你被删除
select * from scott.emp as of timestamp(sysdate -5/1440)
minus
select * from scott.emp ;
还原;
insert into scott.emp
(
select * from scott.emp as of timestamp(sysdate -5/1440)
minus
select * from scott.emp )
扩展题2: 可以建立一视图vemp_15,可以看scott.emp表15分钟的数据
create or replace view vemp_15
as
select * from scott.emp as of timestamp(sysdate -15/1440);
(还可以基于scn号,时间段来查,其他的听下回分解 )
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31098809/viewspace-2124960/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31098809/viewspace-2124960/