flashback系列文章五（flashback table）

最新推荐文章于 2021-05-11 22:58:22 发布

转载最新推荐文章于 2021-05-11 22:58:22 发布 · 124 阅读

本文通过实战演示了如何使用Oracle的闪回表功能撤销错误的数据更新及删除操作，并详细记录了从启用行移动到闪回至指定SCN及时间戳的过程。

SQL> select * from flashback_table_test;

        ID NAME
---------- --------------------------------------------------
       100 thomas
         2 jack
         3 rose
         4 harward

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),dbms_flashback.get_system_change_number from dual;

TO_CHAR(SYSDATE,'YY GET_SYSTEM_CHANGE_NUMBER
------------------- ------------------------
2014-01-01 16:14:40 863613

SQL> update flashback_table_test set id=1 where name='thomas';

1 row updated.

SQL> delete from flashback_table_test where id=4;

1 row deleted.

SQL> commit;

Commit complete.

SQL> flashback table flashback_table_test to scn 863613;
flashback table flashback_table_test to scn 863613
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table flashback_table_test enable row movement;

Table altered.

SQL> flashback table flashback_table_test to scn 863613;

Flashback complete.

SQL> select * from flashback_table_test;

        ID NAME
---------- --------------------------------------------------
       100 thomas
         2 jack
         3 rose
         4 harward

SQL> flashback table flashback_table_test to timestamp to_timestamp('2014-01-01 16:14:40','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from flashback_table_test;

        ID NAME
---------- --------------------------------------------------
       100 thomas
         2 jack
         3 rose
         4 harward

截断表后不能再使用flashbacktable

SQL> truncate table flashback_table_test;

Table truncated.

SQL> flashback table flashback_table_test to scn 863613;
flashback table flashback_table_test to scn 863613
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> flashback table flashback_table_test to timestamp to_timestamp('2014-01-01 16:14:40','yyyy-mm-dd hh24:mi:ss');
flashback table flashback_table_test to timestamp to_timestamp('2014-01-01 16:14:40','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26613085/viewspace-1066188/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26613085/viewspace-1066188/