They are used in conjunction with the database flash recovery area to rollback all transactions on a table (or database wide) to the point in time when the restore point was created.
Restore points are created using the SQL command CREATE RESTORE POINT and by default are stored in the database for 7 days or until the maxinum number of restore points is reached at which time the oldest one is dropped automatically by Oracle. They can also be dropped manually using the SQL command DROP RESTORE POINT. To see what restore points have been created in the database you can query the system view v$restore_point.
To revert to a restore point it's simply a matter of issuing the command FLASHBACK TABLE
Restore Points and Flashback Query Examples
Let's look at an example using the hr schema that is created in the sampledatabase.
First create a restore point. SQL>CREATE RESTORE POINT one;
Next, query the employees table so that we will know whether or not the changes have been undone.
SQL>SELECT first_name FROM employees WHERE employee_id=191;
FIRST_NAME
-------------------
Randall
Now change some data
SQL> UPDATE employees SET first_name='Randy'
2 WHERE employee_id=191;
SQL>COMMIT;
re-query just to be sure.
SQL>SELECT first_name FROM employees WHERE employee_id=191;
FIRST_NAME
-------------------
Randy
Next wait a couple of minutes before reverting to our restore point
SQL>FLASHBACK TABLE employees TO RESTORE POINT one;
Flashback complete
(N.B. row movement has to be enabled before a table can be flashed back. To do this just issue the SQL command 'ALTER TABLE employees ENABLE ROW MOVEMENT')
Re-query again
SQL>SELECT first_name FROM employees WHERE employee_id=191;
FIRST_NAME
-------------------
Randall
The change we made has been undone, let's see if we can redo it by flashing forwards in time.
SQL>FLASHBACK TABLE employees TO TIMESATMP(SYSTIMESTAMP - INTERVAL '2' MINUTE);
Flashback complete
Let's see what data we have in our Oracle database now.
SQL>SELECT first_name FROM employees WHERE employee_id=191;
FIRST_NAME
-------------------
Randy
Magnificent! We went back to the time before we'd made any changes then came forwards to the time after we'd made the change. Could be a great tool for playing tricks on your colleagues!
You can also use the Oracle SQL flashback commands and restore points to rewind the whole database - just replace table with database (the database must be mounted but not open for this).
There are two more things you can do with flashbacks:
- you can see all the versions of the data in a table (or particular column) that existed between two timestamps or system change numbers (scn)
- you can restore tables that have been dropped
Querying versions of data
To do this we need to use the new flashback query clause in the Oracle SQL SELECT statement.In this example we display all the versions that we have of employee #142s first name as well as the start and end tiime of each version (using the pseudo columns versions_starttime and versions_endtime). Notice that the results are stored in reverse chronological order.
SELECT versions_starttime,versions_endtime,first_name FROM employees VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE employee_id=143
/
VERSIONS_STARTTIME VERSIONS_ENDTIME FIRST_NAME
-------------------- -------------------- --------------------
19-MAY-09 16.13.44 sandy
19-MAY-09 16.13.23 19-MAY-09 16.13.44 Randy
19-MAY-09 16.13.02 19-MAY-09 16.13.23 Randall
19-MAY-09 16.13.02 Mandy
We can replace timestamp with SCN and/or use the pseudo columns versions_startscn and versions_endscn as well.
Retrieving dropped tables
This is achieved with an alternative clause of the SQL command FLASHBACK TABLE and the recycle bin introduced with Oracle 10g release 1.sql>DROP TABLE employees; -- table goes into recycle bin
sql>FLASHBACK TABLE employees TO BEFORE DROP; -- table restored
One thing to note is that the table's extents are not released until the table is purged from the recycle bin which can be done manually and is done automatically by Oracle when more space is required in the database.
