Restore point

 A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point.  

Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore  you would lose all changes made not just by your session or your user but all sessions and users in the database. 


CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point.

A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

SQL> CREATE RESTORE POINT before_upgrade;

Guaranteed restore points never age out of the control file and must be explicitly dropped.

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

SQL> DROP RESTORE POINT before_app_upgrade;

V$RESTORE_POINT

CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

UPDATE employees SET salary = salary*10 WHERE employee_id = 108;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000