flashback restore points in oracle


To create a normal restore point, we require either one of the following privileges.
SELECT ANY DICTIONARY
FLASHBACK ANY TABLE
For example:
SQL> CREATE TABLE t (x,y,z)
 2 ENABLE ROW MOVEMENT
 3 AS
 4 SELECT object_id
 5 , object_name
 6 , object_type
 7 FROM all_objects
 8 WHERE ROWNUM <= 5;
Table created.
SQL> SELECT * FROM t;
X Y Z
---------- ------------------------------ -----------------
 20 ICOL$ TABLE
 44 I_USER1 INDEX
 28 CON$ TABLE
 15 UNDO$ TABLE
 29 C_COBJ# CLUSTER
5 rows selected.
creating and using a restore point
We can now create a normal restore point as follows.
SQL> CREATE RESTORE POINT before_we_do_anything;
Restore point created.
SQL> DELETE FROM t WHERE ROWNUM <= 2;
2 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
X Y Z
---------- ------------------------------ -------------------
 28 CON$ TABLE
 15 UNDO$ TABLE
 29 C_COBJ# CLUSTER
3 rows selected.
SQL> FLASHBACK TABLE t TO RESTORE POINT before_we_do_anything;
Flashback complete.
SQL> SELECT * FROM t;
X Y Z
---------- ------------------------------ -------------------
 20 ICOL$ TABLE
 44 I_USER1 INDEX
 28 CON$ TABLE
 15 UNDO$ TABLE
 29 C_COBJ# CLUSTER
5 rows selected.
We have managed to restore our data. If we are finished with the restore point, we can drop it as follows.
SQL> DROP RESTORE POINT before_we_do_anything;
Restore point dropped.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: