create table t1 as select * from dba_objects;SCOTT@ORA12C> select sysdate from dual;SYSDAT
create table t1 as select * from dba_objects;
SCOTT@ORA12C> select sysdate from dual;
SYSDATE
-------------------
2015-09-20 14:54:10
1 row selected.
SCOTT@ORA12C> delete from t1;
0 rows deleted.
SCOTT@ORA12C> commit;
Commit complete.
SCOTT@ORA12C> select count(*) from t1;
COUNT(*)
-----------------
0
1 row selected.
SCOTT@ORA12C> select count(*) from t1 as of timestamp to_timestamp('2015-09-20 14:54:00','yyyy-mm-dd hh34:mi:ss');
COUNT(*)
-----------------
91692
1 row selected.
SCOTT@ORA12C> flashback table t1 to timestamp to_timestamp('2015-09-20 14:54:00','yyyy-mm-dd hh34:mi:ss');
flashback table t1 to timestamp to_timestamp('2015-09-20 14:54:00','yyyy-mm-dd hh34:mi:ss') *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SCOTT@ORA12C> alter table t1 enable row movement;
Table altered.
SCOTT@ORA12C> flashback table t1 to timestamp to_timestamp('2015-09-20 14:54:00','yyyy-mm-dd hh34:mi:ss');
Flashback complete.
SCOTT@ORA12C> select count(*) from t1;
COUNT(*)
-----------------
91692
1 row selected.
注意system表空间下面的表不能闪回,不能跨越DDL语句,
SYS@ORA12C> flashback table t2 to timestamp to_timestamp('2015-09-20 15:07:25','yyyy-mm-dd hh34:mi:ss');
flashback table t2 to timestamp to_timestamp('2015-09-20 15:07:25','yyyy-mm-dd hh34:mi:ss') *
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
但是可以查询:
SYS@ORA12C> select count(*) from t2 as of timestamp to_timestamp('2015-09-20 15:07:25','yyyy-mm-dd hh34:mi:ss');
COUNT(*)
-----------------
91694
1 row selected.
可以重新建一张表或者直接insert:
SYS@ORA12C> create table t3 as select * from t2 as of timestamp to_timestamp('2015-09-20 15:07:25','yyyy-mm-dd hh34:mi:ss');
Table created.
SYS@ORA12C> insert into t2 select * from t2 as of timestamp to_timestamp('2015-09-20 15:07:25','yyyy-mm-dd hh34:mi:ss');
91694 rows created.
@ORA12C> commit;
Commit complete.
--结束END--
本文标题: oracle的闪回查询,闪回删除:
本文链接: https://www.lsjlt.com/news/40255.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0