找回不小心丢失的历史数据

今天有个同事不小心将一个表的全部数据给删除了，其实oracle10g提供了很方便的找回历史数据的方法：

一、给出测试的表和数据

create table T_GRADES ( ID VARCHAR2(20) not null, SUBJECT VARCHAR2(20), GRADE NUMBER(3,1), TEACHER VARCHAR2(20), STUDENTID VARCHAR2(20) ); insert into T_GRADES (ID, SUBJECT, GRADE, TEACHER, STUDENTID) values ('1', '语文', 80, 'Miss_zhang', '050104010121'); insert into T_GRADES (ID, SUBJECT, GRADE, TEACHER, STUDENTID) values ('2', '数学', 90, 'Miss_zhang', '050104010121'); insert into T_GRADES (ID, SUBJECT, GRADE, TEACHER, STUDENTID) values ('3', '数学', 90, 'MR_huang', '050104010120'); insert into T_GRADES (ID, SUBJECT, GRADE, TEACHER, STUDENTID) values ('4', '英语', 90, 'MR_huang', '050104010120'); commit;

二、查看数据和时间

SQL> select * from t_grades; ID SUBJECT GRADE TEACHER STUDENTID -------------------- -------------------- ----- -------------------- -------------------- 1 语文 80.0 Miss_zhang 050104010121 2 数学 90.0 Miss_zhang 050104010121 3 数学 90.0 MR_huang 050104010120 4 英语 90.0 MR_huang 050104010120 SQL> select to_char(sysdate,'yyyymmddhh24miss')now from dual; NOW -------------- 20100112214935 SQL>

三、删除数据并commit可以看到现在已经没有数据了。

SQL> delete from t_grades; 4 rows deleted SQL> commit; Commit complete SQL> select *From t_grades; ID SUBJECT GRADE TEACHER STUDENTID -------------------- -------------------- ----- -------------------- -------------------- SQL>   

四、恢复数据。

SQL> flashback table t_grades to timestamp to_timestamp('2010-01-12 19:10:30','yyyy-mm-dd hh24:mi:ss'); flashback table t_grades to timestamp to_timestamp('2010-01-12 19:10:30','yyyy-mm-dd hh24:mi:ss') ORA-08189: 因为未启用行移动功能, 不能闪回表 SQL> alter table t_grades enable row movement; Table altered SQL> flashback table t_grades to timestamp to_timestamp('2010-01-12 19:10:30','yyyy-mm-dd hh24:mi:ss'); Done SQL> select *from t_grades ; ID SUBJECT GRADE TEACHER STUDENTID -------------------- -------------------- ----- -------------------- -------------------- 1 语文 80.0 Miss_zhang 050104010121 2 数学 90.0 Miss_zhang 050104010121 3 数学 90.0 MR_huang 050104010120 4 英语 90.0 MR_huang 050104010120 SQL>   

五、上述方法需激活表的row movement 属性 ，再给出一种方法：

 SQL> delete from t_grades; 4 rows deleted SQL> commit Commit complete SQL> insert into t_grades select * from t_grades as of timestamp to_timestamp('20100112190000','yyyymmddhh24miss'); 4 rows inserted SQL> commit; Commit complete SQL> select *from t_grades; ID SUBJECT GRADE TEACHER STUDENTID -------------------- -------------------- ----- -------------------- -------------------- 1 语文 80.0 Miss_zhang 050104010121 2 数学 90.0 Miss_zhang 050104010121 3 数学 90.0 MR_huang 050104010120 4 英语 90.0 MR_huang 050104010120

六、上述方法不限制row movement 属性，但是能恢复的数据的时间由参数决定，（单位是：minute）

SQL> show parameter db_flashback_retention_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> alter system set db_flashback_retention_target=2880; System altered SQL> show parameter db_flashback_retention_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 2880

 

 七、结束！