Oracle uses the delete command to delete data recovery

When my colleague retrieved the database, it was Oracle 10 Gb, and the previous deletion method was delete. I don't know how to use trancate.

The SCN (System Change Number) is spelled as System Change Number, which is a very important data structure in the database.

SCN provides an Oracle internal clock mechanism and can be seen as a logical clock, which is crucial for recovery operations.

Note: Oracle only performs Restoration Based on SCN.

It defines the version submitted by the database at a specific time. When a transaction is submitted, it is assigned a unique SCN that identifies the transaction. Some people think that SCN refers to the System Commit Number, which is usually changed when the SCN is submitted. In many cases, these two terms are often used in turn.

Which word is not the most important for us? What is important is that we know that SCN is the internal clock mechanism of Oracle. Oracle maintains Database Consistency through SCN, it also implements an important Oracle recovery mechanism through SCN.

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

1. Original table record $ 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.

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.

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.

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

Article 2

Restore after data deletion by mistake

Select timestamp_to_scn (to_timestamp ('2017-03-13 09:00:00 ', 'yyyy-MM-DD HH: MI: ss') from dual;

Result: 13526973

Converts the deletion time to scn.

Select * from reportinfo

As of scn 13526973

Retrieve the data of the scn point in the reportinfo table

Then, you can perform restoration based on the data.

Create table reporttest as select * from reportinfo where 1 = 0;

Insert in to reporttest select * from reportinfo as of scn 13526973;

-- The above two sentences can be merged.

-- Create table reporttest as select * from reportinfo as of scn 13526973;

This is the reportinfo data of the scn point in the reporttest table. Just process it.