Sunday, November 16, 2008

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;

No comments: