oracle的閃回查詢，閃回刪除：

標籤：oracle閃回

create table t1 as select * from dba_objects;

[email protected]> select sysdate from dual;

SYSDATE

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

2015-09-20 14:54:10

1 row selected.

[email protected]> delete from t1;

0 rows deleted.

[email protected]> commit;

Commit complete.

[email protected]> select count(*) from t1;

COUNT(*)

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

0

1 row selected.

[email protected]> select count(*) from t1 as of timestamp to_timestamp(‘2015-09-20 14:54:00‘,‘yyyy-mm-dd hh24:mi:ss‘);

COUNT(*)

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

   91692

1 row selected.

[email protected]> flashback table t1 to timestamp to_timestamp(‘2015-09-20 14:54:00‘,‘yyyy-mm-dd hh24:mi:ss‘);

flashback table t1 to timestamp to_timestamp(‘2015-09-20 14:54:00‘,‘yyyy-mm-dd hh24:mi:ss‘)                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

[email protected]> alter table t1 enable row movement;

Table altered.

[email protected]> flashback table t1 to timestamp to_timestamp(‘2015-09-20 14:54:00‘,‘yyyy-mm-dd hh24:mi:ss‘);

Flashback complete.

[email protected]> select count(*) from t1;

COUNT(*)

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

   91692

1 row selected.

注意system資料表空間下面的表不能閃回，不能跨越DDL語句，

[email protected]> flashback table t2 to timestamp to_timestamp(‘2015-09-20 15:07:25‘,‘yyyy-mm-dd hh24:mi:ss‘);

flashback table t2 to timestamp to_timestamp(‘2015-09-20 15:07:25‘,‘yyyy-mm-dd hh24:mi:ss‘)                *

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

但是可以查詢：

[email protected]>  select count(*) from t2 as of timestamp to_timestamp(‘2015-09-20 15:07:25‘,‘yyyy-mm-dd hh24:mi:ss‘);

COUNT(*)

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

   91694

1 row selected.

可以重建立一張表或者直接insert：

[email protected]> create table t3 as select * from t2 as of timestamp to_timestamp(‘2015-09-20 15:07:25‘,‘yyyy-mm-dd hh24:mi:ss‘);

Table created.

[email protected]> insert into t2 select * from t2 as of timestamp to_timestamp(‘2015-09-20 15:07:25‘,‘yyyy-mm-dd hh24:mi:ss‘);

91694 rows created.

@ORA12C> commit;

Commit complete.

oracle的閃回查詢，閃回刪除：