Oracle Learning (23) Flash back operation (flashback)

Oracle's flash-back functionality restores a specified table in the event of an incomplete recovery of the database. The flashback database is a new method for point-in-time recovery, which enables you to quickly restore Oracle to a previous time to correct problems caused by logical data corruption or user errors. When recovery is required, the database can be restored to a point in time before the error and only the changed block of data is restored.

Flash-back operations in Oracle include the following 4 types:

(1) Query Flashback: Query the past a specified time, the specified Entity data, restore the wrong database update, delete and so on.

(2) Table flashback: Returns the table to the state of a certain time in the past, restores the table, and cancels the modification to the table.

(3) Delete flashback: The deleted table can be restored.

(4) Database flashback: The entire database can be rolled back to a point in the past.

1, Query Flash back

Query flashback can look at any data in the past, if you want to query a table at a certain point in time, you can position the query target object to the table at a certain moment of the table, the table at a particular table can be expressed as follows:

table_name as of timestamp real_timestamp; --it represents a table as a whole * *

For example, to query the status of the person table in 2012-6-2 19:00:00, you can use the following statement:

SELECT * from person as of timestamp to_timestamp (' 2012-6-2 19:00:00 ', ' yyyy-mm-dd HH24:mi:ss ');

After knowing the table at a certain point in time, we can easily restore the table to a certain moment, for example, we deleted 3 records from the person table at the time of 2012-6-2 19:00:00, and then I wanted to restore it. Using the query flash back we can do this:

Method One:

The first step is to delete all the data in the current table: delete from person;

In the second step, we take the data before the point of 2012-6-2 19:00:00 and insert it into the current table through a query flashback, and the calling statement is as follows:

INSERT into the person select * from person as of timestamp to_timestamp (' 2012-6-2 18:59:59 ', ' yyyy-mm-dd HH24:mi:ss ');

Method Two:

First find the records that were deleted from the person table, and then insert them into the person table, the following SQL statement:

INSERT into a person select * from person as of timestamp to_timestamp (' 2012-6-2 18:59:59 ', ' yyyy-mm-dd HH24:mi:ss ') p where NOT EXISTS (SELECT * from person where id=p.id);

Because the query flashback is related to time, so here's another setting, where the set time in Sqlplus can be turned on to turn on the timing display, which causes the statement's execution time to be displayed before the statement every time it is executed, using set times The off statement can turn off the feature.

2, Table Flash back

The table flashback can easily be used to cancel the modification of the table. Only Oracle's Enterprise version can perform a table flashback.

The table flash back must be row movement to enable.

The syntax format for the table flashback is as follows:

Flashback table [schema.] table_name[,... N] to {[SCN] | [Timestamp] [[Enable | disable] triggers]};

which

SCN: Represents the system change number, which can be queried from the Flashback_transaction_query data dictionary.

Timestamp: Indicates a flashback in the form of a timestamp.

Enable|disable triggers: Indicates the state after the trigger is restored, and defaults to disable.

Sample code:

(1) Ensure that the table row movement needed to be flashed back is enable:

ALTER TABLE Hello enable row movement;

(2) Flash back to the table:

Flashback table Hello to timestamp to_timestamp (' 2012-6-3 14:00:00 ', ' yyyy-mm-dd HH24:mi:ss ');--recovery form to 2012-6-3 14:00:00 this time the appearance of * *