Summary of oracle accidental deletion and data recovery methods

If the user accidentally deletes/updates the data, there is no direct way for the user to recover the data, they must seek help from the DBA to recover the database to Oracle9i, this embarrassing situation has improved. Oracle 9i provides a new technical means-Flashback query. You can use flashback query to obtain data before misoperation in a timely manner and perform corresponding recovery measures for errors, all of this requires no DBA intervention.

I deleted the production data because of a temporary embarrassment.

The delete statement is used, and commit is quickly returned.

The following two statements:

The Code is as follows:	Copy code
Alter table tablename ENABLE row movement; Flashback table tablename to timestamp to_timestamp ('2017-09-13 13:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');

 

------------------------------------------------------

Remember the delete --- commit statement that runs around half past two;

The specific execution process can be seen from the following examples;

1. Original table records

The Code is as follows:	Copy code
$ Sqlplus eygle/eygle SQL * Plus: Release 10.1.0.2.0-Production on Wed Mar 30 08:52:04 2005 Copyright (c) 1982,200 4, Oracle. All rights reserved. Connected: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production With the Partitioning, OLAP and Data Mining options SQL> select count (*) from t1; COUNT (*) ---------- 9318

2. accidentally delete all records
And submit the changes.

The Code is as follows:	Copy code
SQL> delete from t1; 9318 rows deleted. SQL> commit; Commit complete. SQL> select count (*) from t1; COUNT (*) ---------- 0

3. Obtain the current SCN
If you know exactly that the SCN is the best before deletion, if you do not know, you can try a flashback query.

The Code is as follows:	Copy code
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 10671006 SQL> select count (*) from t1 as of scn 10671000; COUNT (*) ---------- 0 SQL> select count (*) from t1 as of scn 10670000; COUNT (*) ---------- 9318

We can see that when SCN = 10670000, the data is in.

4. Restore data.

The Code is as follows:	Copy code
SQL> insert into t1 select * from t1 as of scn 10670000; 9318 rows created. SQL> commit; Commit complete. SQL> select count (*) from t1; COUNT (*) ---------- 9318

Tutorials for other users

For database operations, you must add where after the delete operation ". Today, I accidentally saw a message on the Internet about oracle accidental deletion of data recovery, which is really helpful. Next I will report my test to you.

 

The Code is as follows:

Copy code

1. select * from t_viradsl2 t // query all data in t_viradsl2. Three data entries are displayed. 2. delete t_viradsl2 // delete all data in t_viradsl2 and the three data items disappear. 3. select * from t_viradsl2 t // no data. 4. insert into t_viradsl2 select * from t_viradsl2 as of timestamp to_Date ('2017-01-19 15:28:00 ', 'yyyy-mm-dd hh24: mi: ss ') // The accidentally deleted data has been inserted into the table. 5. select * from t_viradsl2 t // three more data entries are displayed.

Let's analyze the fourth step. Pay attention to this sentence:

Select * from t_viradsl2 as of timestamp to_Date ('2017-01-19 15:28:00 ', 'yyyy-mm-dd hh24: mi: ss'). What does that mean, find all the data at the time point t_viradsl2 at 15:28:00. Now that you have found the data, you can do everything you want.

Here, I would like to share with you