上一回演示了运用闪回表查询恢复delete删除的数据以及其原理,今天了解下闪回表。
原理:
闪回查询只是查询以前的一个快照而已,并不改变当前表的状态。
闪回表则是将恢复当前表及附属对象一起回到以前的时间点。
特性:
1.
2.
3.
4.
5.
语法:
运用闪回表前提
示例:
SQL> create table tab_test as select * from all_objects;
Table created
2.查询tab_test表中数据量
SQL> select count(*) from tab_test;
----------
3.为tab_test表创建索引和触发器(触发器为null,不做任何操作)
SQL> create index ind_test on tab_test(object_name);
Index created
SQL> create or replace trigger tr_test
Trigger created
4.记录当时的时间点,试图恢复到该时间点
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME
------------------- ----------------------------------------
2010-06-30 23:02:37 1160764
5.删除tab_test表中数据
SQL> delete from tab_test;
40699 rows deleted
SQL> commit;
Commit complete
6.查询删除数据后的tab_test,确定其表中已没有数据
SQL> select count(*) from tab_test;
----------
7.删除tab_test表中索引ind_test
SQL> drop index ind_test;
Index dropped
8.更改tr_test触发器
SQL> create or replace trigger tr_test
Trigger created
9.确保该表中的行迁移(row movement)功能
SQL> alter table tab_test enable row movement;
Table altered
10.恢复tab_test表到刚记录的时间点(或scn),由于表中存在触发器,因此使用了关键字enable triggers;
SQL> flashback table tab_test to timestamp to_timestamp('2010-06-30 23:02:37', 'yyyy-mm-dd hh24:mi:ss') enable triggers;
Done
11.查看恢复结果如下:
SQL> select count(*) from tab_test;
----------
SQL> select index_name from user_indexes where table_name = 'TAB_TEST';
INDEX_NAME
------------------------------
SQL> select object_name, status from user_objects where object_name in('TR_TEST', 'IND_TEST');
OBJECT_NAME
------------------ -------
TR_TEST
SQL> set pages 0
SQL> set lines 1000
Cannot SET LINES
SQL> set long 2000
SQL> select text from user_source t where t.name = 'TR_TEST' order by line;
trigger tr_test
after insert on tab_test
for each row
begin
end tr_test;
总结:
1.
2.
3.
4.
5.
6.