Oracle Data recovery method (Commit transaction is also possible)

Today in the operation of the database, found that the data operation error, want to recover, but no transaction, supposedly, set to not default commit transaction, at this time the various operations do not respond to the database. At this point, you can rollback the transaction and undo any uncommitted changes. However, once a commit is made, there is really no way to undo it. Fortunately, Oracle also has a timestamp method.

The first method:

1. Open the Flash storage permissions
ALTER TABLE tablename ENABLE row movement;
2. Restore the table to a specified point in time
Flashback table TableName to timestamp to_timestamp (' 2011-02-28 10:40:00 ', ' yyyy-mm-dd hh24:mi:ss ');
The following parameter is the point in time to restore

Second: Use Oracle's snapshots to find data at a point in time
SELECT * FROM TableName as of TIMESTAMP (Systimestamp-interval ' MINUTE)

Or

SELECT * FROM tablename as of timestamp to_timestamp (2011-05-21 11:40:00 ', ' yyyy-mm-dd HH24:MI:SS ');

This allows you to query the data for the specified time period and then copy the queried data to the original table.

Cases:

If we mistakenly deleted the data from the table EMP in the first 5 minutes, we can do the following:

Retrieve raw Data

Select * from EMP as of timestamp sysdate-6/1440

Delete the original table after deleting and creating a backup table

Create table emp As (SELECT * from emp as of timestamp sysdate-6/1440);

Can do poor, find deleted records = 6 minutes ago Raw data-now the records in the table are then inserted into the original table submitted

Insert into EMP

Select * FROM

(

SELECT * from emp as of timestamp sysdate-6/1440 minus Select * from EMP)

--timestamp (AGS) method

--interval (interval) type used to store the time interval between two timestamps

--When you want to reply to the deleted data

--query data up to 500 minutes ago

SELECT * from EMP as of timestamp (Systimestamp-interval ' minute)

--query data up to 600 seconds ago

SELECT * from EMP as of timestamp (Systimestamp-interval ' second)

--Query data up to 5 hours ago

SELECT * from EMP as of timestamp (systimestamp-interval ' 5 ' hour)

--query data from the previous day

SELECT * from EMP as of timestamp (systimestamp-interval ' 1 ' Day)

--query data 1 minutes ago (1 days = 1440 minutes) date-number=date

SELECT * from emp as of timestamp sysdate-1/1440

--Query data 5 hours ago

SELECT * from emp as of timestamp sysdate-5/24

--Query data within 5 hours = data in the previous table deleted 5 hours ago-data in the table now

--minus minus

SELECT * FROM

(SELECT * from emp as of timestamp sysdate-5000/1440 minus select * from EMP)

From 9i onwards, Oracle provides the flashback (flashback) feature. Use the Flashback table statement to read past images of the table from the Undo section (undo Segmeng) and rebuild the table rows with the Flashback query feature introduced in Oracle9i. The undo_retention gives the minimum time to support the flash back. That is to say, flashback can support at least undo_retention time, if the system is more idle, it can go back to a longer time. If the system is busy, it is possible to reuse the data space that has not yet reached undo_retention time absorption. Note: One of the prerequisites for using a flashback is that the table cannot perform DDL operations, not just the DDL operation, but also the data before the DDL operation can be flashed back.

Note: Do not start the line movement function, can not flash back to the table

Alter table emp enable row movement;

The purpose of this command is to allow Oracle to modify the ROWID assigned to the row. In Oracle, when a row is inserted, it is assigned a rowid, and this line will always have this rowid. The Flashback table processing completes the delete operation on the EMP table and inserts the rows again, so that the rows are assigned a new ROWID. To support the Flashback feature, Oracle must be allowed to perform this operation.

Original: http://blog.csdn.net/jiajane/article/details/49280277

Oracle Data recovery method (Commit transaction is also possible)