About Oracle retrieval of erroneous erase data

For a development, when the project is about to go online before the test phase, this process will often have some bugs, then we will generally be on the customer site to directly solve these problems. Then we may connect a lot of databases, including the customer production database, on the machine at the customer site. Sometimes when we are connected to work for more than a dozen hours, due to people over-fatigue, may cause a mistake to delete the customer production database, then some very important data by our one accidentally deleted, for many developers, think more about is there is no backup database? The backup database is re-directed to the production environment, etc... So, what if the customer doesn't have a backup? So what do we do? Node recovery database, this is the perfect choice.

Then, the next step is to share a node recovery database method:

-- 1. Gets the node for the current database store, which needs to be explained. Nodes are stored as long as the database is started Select dbms_flashback.get_system_change_number from  dual --  node number  199360598464-- 2. Create a data table   storage node number   and    Corresponds to the node number when the database data bar number create table temp (count int, scn int);-- 3. Create a task   given a range of nodes   Loop node number and query the current node database data bars are stored in the created table Declarei long :=199360598464;begin     for  i in 199360597000..199360598464 loop     insert into  Temp (SCN)  valus (i);      update temp set count= (Select count ( *)  from t_b_note_order as of scn i)  where scn=i;     end loop;    end;    -- 4. View the largest one of the data in the database   such as scn= 199360598400--  5. Node number found    so we can start recovering the data. Insert into t_b_note_order select  * from t_b_note_order as of scn 199360598400 

At this point, congratulations, your data has been restored 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0057.gif "alt=" J_0057.gif "/>

But there are many times, we mistakenly delete the data immediately after the deletion of data mistakenly, in this case we do not need to build table storage node number and corresponding node Number data bar number ...

--1. Gets the node of the current database store, which needs to be explained as long as the database is started, the node select Dbms_flashback.get_system_change_number from dual-node number 199360598464--2. Self-propelled partial node number database bar number on it-query the given node number under Table Data Bar select COUNT (*) from T_b_note_order as of SCN 199360574964--3. Locate the Database The maximum number of nodes that exist data such as 199360574964--4. Recovering data INSERT INTO T_b_note_order SELECT * from T_b_note_order as of SCN 199360574964

At this point, congratulations, your data has been restored 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0057.gif "alt=" J_0057.gif "/>

Note: T_b_note_order is mistakenly deleted data table name

Although we have a way to recover the data mistakenly deleted, but as a programmer, still need to be careful, careful attitude to do their job.

This article is from "The rookie in the struggle ..." Blog, declined reprint!

About Oracle retrieval of erroneous erase data