Use flashback query to delete table data by mistake

[Application Scenario ]:1And Operation errorsDelete,UpdateThe data has been submitted.

2You have made changes to a table and want to see which data has changed.

3 . deleted by mistake function , procedure , trigger and so on

[Environment ]:Oracle 10g r1
,Oracle 10g r2,Oracle 11g r1,
Oracle 11g r2

[Note ]:( 1 ) Based on Undo
You must note that DDL
Operation impact. After modifying and submitting data DDL
Operations, including: Drop/modify
Column , Move
Table , Drop
Partition ( If yes ), Truncate table/partition . Undo
The undo data in the tablespace is invalid. It is applied to tables that have performed these operations. Flashback Query
Will trigger ORA-01466
Error.

Other table structure modification statements do not affectUndo
Undo records in the tablespace, but may be caused by table structure modification.Undo
Repeat records that cannot be applied. For example, if a constraint is addedFlashback Query
The queriedUndo
The record does not meet the new constraints. It is obviously impossible to restore the record directly at this time.Disable
Constraints, either through appropriate logic, after processing the data to be restored, then execute recovery.

( 2 ) Based on Undo
Table recovery, Flashback table
What we actually do is DML
Operation ( Add DML
Lock ) , So pay attention Triggers
The effect on it. By default, Flashback table to SCN/Timestamp
Automatically Disable
Drop the operation table Triggers If you want Trigger
Can continue to use, you can Flashback table
Append Enable triggers
Clause.
Flashback Query Minute Flashback query, flashback VERSION Query ,
Flashback transaction Query Three.

If truncate
Delete table data or drop user XXX cascade; in this case, you can only use flashback
database ( disabled by default), imp/impdp ( must be backed up in advance), RMAN restore (Backup set required), logminer
log mining (archiving required ). It also causes a lot of white work.

This article mainly describes the method mentioned by Comrade Rodin last time, which is also the most commonly used: for example, there isADelete table data, but I want to retrieve the deleted data.

Syntax:Select * From tablename as of Timestamp
To_timestamp ('2017-01-11 11:10:17 ', 'yyyy-MM-DD hh24: MI: ss ');

 

SQL> Create Table A (ID number );

SQL> INSERT INTO A values (1 );

 

1 row created.

 

SQL> INSERT INTO A values (2 );

 

1 row created.

 

SQL> INSERT INTO A values (3 );

 

1 row created.

 

SQL> INSERT INTO A values (4 );

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * From;

 

ID

----------

1

2

3

4

Simulate user misoperations and delete data

 

SQL> Delete from;

 

Deleted4Line.

 

SQL> commit;

 

Submitted.

 

SQL> select * From;

 

Unselected row

 

View the status before deletion: assume that the deleted data has been deleted5
About minutes:

 

SQL> select * from a as of Timestamp sysdate-5/1440;

 

ID

----------

1

2

3

4

Or:

SQL> select * from a as of Timestamp to_timestamp ('2017-01-13 12:00:16 ', 'yyyy-MM-DD hh24: MI: ss ');

 

ID

----------

1

2

3

4

UseFlashback QueryData before restoration:

 

SQL> insert into a select * from a as of Timestamp to_timestamp ('2017-01-13 12:00:16 ', 'yyyy-MM-DD hh24: MI: ss ');

Created4Line.

 

SQL> commit;

 

Submitted.

 

SQL> select * From;

 

ID

----------

1

2

3

4

As shown in the preceding example,As of Timestamp
Really easy to use.

To the endFlashback Query
How long can it be queried?

It depends onUndo_retention.--Default900Seconds,15Minutes.

However, it does not mean that15Minutes.Undo_retentionWhen there is sufficient space"Minimum retention time". You can test this.

For more information, pleaseGoogle,Baidu
For more information, see the official documentation.

Refer to the official documentation for this article:

Oracle Database

Advanced Application Developer's Guide

11G
Release 2 (11.2)

E17125-05

 

Using Oracle Flashback Technology

Using Oracle flashback query (select as)