Summary of Oracle accidental deletion data recovery method _oracle

If the user mistakenly deletes/updates the data, as the user does not have any direct method to restore, they must help the DBA to restore the database, to the oracle9i, this embarrassing situation has improved. A new technical tool in the Or (www.jb51.net) acle 9i is a flash-back query that allows users to obtain data before the error in a timely manner using a flash-back query, and to recover the appropriate corrective action without DBA intervention

Because the hand is cheap, the production data was deleted by me.

With the DELETE statement, and it's quickly returned to the commit.

The following two statements:

ALTER TABLE tablename ENABLE row movement;
Flashback table TableName to timestamp to_timestamp (' 2012-09-13 13:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Remember it's about two o ' clock to run the delete---commit;

The implementation process can be realized from the following sample diagrams;

1. Record of the original table

 $ sqlplus eygle/eygle
sql*plus:release 10.1.0.2.0-production on Wed Mar 08:52:04
(c) 1982, 20 Oracle.  All rights reserved.
Connected to:
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. Delete all records by mistake
and commit the changes.

Sql>delete from T1;
9318 rows deleted.
sql>commit;
Commit complete.
Sql>select Count (*) from T1;
  COUNT (*)
----------
     0

3. Get current SCN
If you can know exactly what the SCN is best before removing it, you can try the flashback query if you don't know.

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 see the data in the scn=10670000.

4. Restore data.

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 netizens

For database operations, delete must be followed by the where. Today, I accidentally saw a message about the recovery of Oracle mistakenly deleted data, and found that it is really good, the following on my test to report to you.

 1. Select * FROM T_VIRADSL2 t     //query T_VIRADSL2 all the data, you can see three data
 2. Delete T_VIRADSL2           //Delete all the data in the T_VIRADSL2, Three data disappears
 3. Select * FROM T_VIRADSL2 t     //No data.
 4. Insert INTO T_VIRADSL2 SELECT * T_VIRADSL2 as of timestamp to_date (' 2011-01-19 15:28:00 ', ' Yyyy-mm-dd hh24:mi : SS ')       //Insert the mistakenly deleted data into the table
 5. Select * FROM T_VIRADSL2 t     //And you will see three data.

Let's analyze the fourth step and pay attention to this sentence:

SELECT * from T_VIRADSL2 as of timestamp to_date (' 2011-01-19 15:28:00 ', ' yyyy-mm-dd hh24:mi:ss '), what does that mean, find T_VIRADSL2 in 2011 -01-19 15:28:00 All the data for this point in time, now that you've found it, you can do whatever you want.

Here to share with you