oracle闪回查询恢复delete删除数据（Oracle flashback query delete data recovery delete）

oracle闪回查询恢复delete删除数据（Oracle flashback query delete data recovery delete） oracle闪回查询恢复delete删除数据(Oracle flashback query delete data recovery delete) Oracle flashback query delete data recovery delete Flashback query (flashback query) principle Oracle, based on undo information, uses undo data, like a consistent read method, to put tables at a time point (or SCN) before deletion, so that data is retrieved. Flashback query (flashback query premise): SQL> show parameter undo; NAME TYPE VALUE ----------------------------------------------------------------------------- Undo_management string AUTO Undo_retention integer 900 Undo_tablespace string UNDOTBS1 Where undo_management = auto, set the automatic undo management (AUM), the parameter is set as default: auto; Undo_retention = n (seconds), setting determines the maximum storage time of the undo. The larger the value, the more undo table space support is required. The commands for modifying undo_retention are as follows: SQL>, alter, system, set, undo_retention = 3600; System altered Flashback way 1. obtain a point or time before the data is deleted, as follows: scn: SQL>select, to_char (sysdate,'yyyy-mm-dd, hh24:mi:ss'), time, to_char (dbms_flashback.get_system_change_number), SCN, from, dual; time scn ------------------- ---------------------------------------- 2010 - 06 - 29 23: 03: 14 1060499 2. 查询该时间点 (或scn) 的数据, 如下: sql select * from t > as of timestamp to _ timestamp (2010 - 06 - 29 22: 57: 47 ',' yyyy - mm - dd hh24: mi: ss'); sql select * from t > as of scn 1060174; 3. 将查询到的数据, 新增到中.也可用更直接的方法, 如: sql create table tab > _ test as select * from t of timestamp to _ timestamp (2010 - 06 - 29 22: 57: 47 ',' yyyy - mm - dd hh24: mi: ss'); sql insert into tab > _ test select * from1060174; 示例: create table t (id number); insertinto t values (1); insert into t values (2); insert into t values (3); insert into t values (4); insert into t values (5); 1.查看t表中的原始数据 sql > select * from t; id --------- 1 2 3 4 5 2.获取数据删除前的一个时间点或scn sql > select to _ char (sysdate, 'yyyy - mm - dd hh24: mi: ss') time, to _ char (dbms _ flashback.get _ system _ change _ number) scn from dual; time scn ------------------- ---------------------------------------- 2010 - 06 - 29 23: 23: 33 1061279 3.删除t表中的数据, 并提交事物 sql > delete from t; 5 rows deleted sql > commit; commit complete 4.在查看t表, 此时t表中数据以删除 sql > select * from t; id ---------- 5.查看t表中scn为1061279 (或时间点为2010 - 06 - 29 23: 23: 33) 时的数据 sql select * from t > as of scn 1061279; id ---------- 1 2 3 4 5 6.确认要恢复后, 将t表中的数据还原到scn为1061279 (或时间点为2010 - 06 - 29 23: 23: 33) 时的数据, 并提交事物 sql insert into t > select * from t as of scn 1061279; 5 rows inserted sql > commit; commit complete 7.确认t表数据的还原情况 sql > select * from t; id ---------- 1 2 3 4 5 注: 推荐使用scn, 由于oracle9i中, 因为scn与时间点的同步需要5分钟, 如果最近5分钟之内的数据需要falshback query查询, 可能会查询丢失, 而scn则不存在这个问题. This problem has been corrected in Oracle10g (SCN's approximate relation to time points, which can be obtained through LOGMNR analysis archive logs). Limitations of Falshback query queries: 1. can not Falshback data up to 5 days ago. Before the 2. flashback query cannot be restored to the table structure change, because the flashback query using a current data dictionary. 3. affected by the undo_retention parameter, for undo_retention's previous data, Flashback does not guarantee success in Flashback. 4., drop, truncate, etc. do not record rollback operations, can not be restored. 5. ordinary users use the dbms_flashback package and must be authorized by the administrator. The commands are as follows: SQL>grant, execute, on, dbms_flashback, to, scott;