Oracle's flashback query, flash back delete:

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.

Note the table below the system table space cannot be flashed back and cannot span DDL statements.

[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

However, you can query:

[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.

You can re-build a table or insert directly:

[Email protected]> CREATE TABLE t3 as select * from T2 as of timestamp to_timestamp (' 2015-09-20 15:07:25 ', ' yyyy-mm-dd h H24: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's flashback query, flash back delete: