Test case: flashback table

scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t(a int);

Table created.

scott@TEST10G> insert into t values(1);

1 row created.

scott@TEST10G> insert into t values(2);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select sysdate from dual;

SYSDATE
-------------------
2008-11-16 13:23:44


scott@TEST10G> insert into t values(3);

1 row created.

scott@TEST10G> insert into t values(4);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2
         3
         4


scott@TEST10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

scott@TEST10G> alter table t enable row movement; 

Table altered.

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');

Flashback complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2



Must enable row movment:

scott@TEST10G> alter table t disable row movement;

Table altered.

scott@TEST10G>
scott@TEST10G> select table_name, row_movement from user_tables where table_name='T';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
T                              DISABLED

7 rows selected.

scott@TEST10G> select * from t;

         A
----------
         1
         2

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');
flashback table t to timestamp to_date('2008-11-16 13:23:44')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


scott@TEST10G>

Note: to flashback to 15 min ago, we can use the following syntax:

SQL> flashback table hr.employees, hr.departments
to timestamp systimestamp - interval '15' minute;