Oracle Database misoperation executed Delete, how do I recover the data? ___ Database

As a programmer, database operation is necessary, but if the operation is wrong, generally will cause more serious consequences.

Today, accidentally, did a worried thing, will be using the organization of the wrong operation of the delete, suddenly the brain a little hypoxia, feeling a circle (I believe that no data backup and rollback experience of the small partners are such feelings), but a think, the database is generally supporting data rollback, Then look up the solution.

Because Oracle database is used, this is very good, because Oracle has flash-back functionality, instantaneous light.

There are two solutions to this problem:

One: Restore according to TIME:

1, query the current time of the database (the purpose is to check whether the database time is similar to your computer time, avoid time difference and restore the data to the wrong time point)

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2, query delete data before the time point data

SELECT * from table name as of timestamp To_timestamp (' 2016-08-11 16:12:11 ', ' yyyy-mm-dd hh24:mi:ss ');

(If there is no data, the time will continue to advance)

3. Recover data (exciting moments)

Flashback table name to timestamp to_timestamp (' 2016-08-11 16:12:11 ', ' yyyy-mm-dd hh24:mi:ss ');

Successful, data recovery success;

However, there may be problems, such as error: ORA-08189: Do not enable row mobile function, can not flash back table;

Do not be afraid, this is very simple;

ALTER TABLE name enable row movement;

Then execute the above SQL again;

Second: Recover data from the database SCN

1, query the current database SCN number

Select CURRENT_SCN from v$database (switch to SYS user or system user query if not executed)

The current value of the query is: 91799986

2, reduce the SCN number query deleted table data (if no data continue to shrink SCN, because the database operation more than one person, the SCN number changes more, can be reduced a few number)

SELECT * from table name as of SCN 91799980;

3. Recover data

Flashback table name to SCN 91799980;

Restore completed. If error: ORA-08189: Do not enable row mobile function, can not flash back table;

The mood finally can calm down.

For other database misoperation execution Delete, there should also be a corresponding solution, for the time being not yet tried.