Oracle's Flash Back

The first part of this paper is a plsql program, which realizes the salary increase for different positions, changes the data of the database. and commits the transaction; the second half combines the previous example with the data recovery in Oracle, that is, how to undo the already-commit operation. Also due to my limited technical level and expression capacity, for the conceptual part and summary from excerpt from: Http://www.cnblogs.com/chengxiao/p/5860823.html, If you want to learn more about the flashback mechanism in Oracle, you can access the address of the blog post, this article is focused on the example demo

First, the need is implemented in the EMP table:

To raise wages for employees, the president Rose 1000 manager rose 800 others rose 400

Check the data in the EMP table before executing the program:

The payroll for Jobs for clerk is 800.

The payroll for Jobs for president is 5000.

In accordance with the increase in wages for employees, the president Rose 1000 manager rose 800 Other 400 demand write the following Plsql program statements are as follows:

Set Serveroutput on

Declare

Vjob Emp.jobs%type;

Vmpno Emp.empno%type;

--Define cursor Mysur

Cursor Mysur is a select jobs,empno from EMP;

Begin

Open Mysur;

Loop

Fetch Mysur into vjob,vmpno;

Exit when Mysur%notfound;

--Cycle for judgment, raise salary for employees of different positions

If vjob= ' president ' then update EMP set sal=sal+1000 where Empno=vmpno;

elsif vjob= ' MANAGER ' then update emp set sal=sal+800 where Empno=vmpno;

Else update emp set sal=sal+400 where Empno=vmpno;

End If;

End Loop;

Close Mysur;

--Commit a transaction

Commit

Dbms_output.put_line (' execution complete ');

End

/

After executing the program, the data in the EMP table is:

You can see that the salary for jobs for clerk is 1200 up 400.

Jobs ' salary for president is 6000 up 1000.

Second, the above is the PLSQL program here no longer described below through the EMP table data specific demonstration of Oracle data recovery:

Based on the author's limited technical level, this summary briefly first the flashback table in the flashback query, for other types of flashback can be viewed under the final PS Content section

Flashback table: is to flash a data table back to a certain point in time

The steps are taken in conjunction with the EMP data sheet above, according to the above operation of the EMP table data has been

is changed and commits the transaction, and this time we consider the use of Oracle's flashback mechanism to revoke

Actions that have been submitted:

Steps

First, the demand is implemented in the EMP table, to the employees pay rise, the president Rose 1000 manager rose 800 others rose 400

And it's not going to happen again. What is the data in the EMP table before you do that? Here's a look at:

:

　

(a) First, flash back to a point in time to check the data in the table to determine whether to flash back to that point in time:

(SQL1:) SELECT * FROM emp

As of timestamp to_timestamp (' 2017-9-16 00:30:00 ', ' yyyy-mm-dd hh24:mi:ss ');

This statement flashes the table back to 2017-9-16 00:30:00 at this point in time and then look at the data table

Whether the data is the result you want:

Can see that the data in the EMP table at this point in time is not modified state

(b) If you determine that the data in the table that you are querying at this point in time is the result you want, do a flashback table operation:

But first, set the table to support row movement:

(SQL1:) ALTER TABLE EMP enable row movement;

Then perform a flashback operation:

(SQL2:) Flashback table EMP

to timestamp to_timestamp (' 2017-9-15 08:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

This completes the undo for the EMP table:

The results of the above two-line statements are:

Table EMP has changed.

Table EMP succeeded.

Check the data in the EMP table to see:

The payroll for Jobs for clerk is 800.

The payroll for Jobs for president is 5000.

Done!

PS: Introduction to Oracle's flashback mechanism:

Flash back

Concept: Flashback technology is part of Oracle's powerful database backup recovery mechanism, which provides fast, minimal loss of recovery in the event of a logical error in the database.

Undo Segment

Before you talk about flashback technology, you need to understand a logical structure in Oracle-the undo segment . Because most flashback technologies need to rely on revocation data in the undo segment. Revoking data is the information required to reverse the results of a DML statement, and as long as a transaction modifies the data, the original data before the update is written to an undo segment. (The transaction rollback also uses the data in the Undo segment). When a transaction starts, Oracle assigns it a undo segment, and there is a many-to-one relationship between the transaction and the undo segment, that is, a transaction can only have one undo segment, and multiple transactions may share an undo segment (although this typically does not happen when the database is running normally)

Flash Back Technology :

Oracle provides four available flash back technologies (flashback query, flashback Delete, flashback file, Flashback database), each with different underlying architecture support, but in fact these four different flashback technology part of the function is overlapping, use also need to choose the most suitable flashback function according to the actual scene.

PS Content excerpt from: http://www.cnblogs.com/chengxiao/p/5860823.html

Oracle's Flash Back