In this simple example, we'll use a normal restore point to flash back a table to our testing starting point after it has been modified during the testing of a new procedure. For simplicity's sake, the procedure will only delete rows from our table, but it could just as easily have been performing complicated business logic. Because we have flashback logging disabled, it isn't possible for us to perform a flashback database (we'll receive a 'ORA-38726: Flashback database logging is not on.' if we attempt it).

Because we'll be using flashback table, we need to enable row movement for the 'testuser.test' table involved, in effect giving Oracle permission to modify the ROWIDs for the rows in our table when it moved things around during the flashback table.

SQL> alter table testuser.test enable row movement;

Table altered.

Create the 'process_data' procedure, representative of a new batch process we're testing:

SQL> create or replace procedure testuser.process_data is

2 begin

3 delete from testuser.test where id > 50;

4 commit;

5 end;

6 /

Procedure created.

Next, we'll create a normal restore point 'before_test1' to mark our starting point for testing. This will allow us to return to this point in time to make changes and retest our new batch process without having to note the SCN or guess at the timestamp:

SQL> create restore point before_test1;

Restore point created.

Next, we'll execute the "process_data" procedure. After the procedure has completed, we see that the data has been processed correctly, and only 50 rows remain in the testuser.test table:

SQL> exec testuser.process_data;

PL/SQL procedure successfully completed.

SQL> select count(*) from testuser.test;

COUNT(*)

----------

50

We've completed testing for the first iteration of our batch process. To allow testing of the procedure to run against the testuser.test table once again, we'll use our restore point to flash back the table to our starting point, and begin the subsequent rounds of testing:

SQL> flashback table testuser.test to restore point before_test1;

Flashback complete.

Once we've completed the testing and our restore point is no longer needed, we're free to drop the restore point from the database. Note that this step isn't actually required, as the restore points will automatically age out of the database, but it's probably a good idea to keep things as tidy as possible. We'll drop the restore point to minimize confusion:

SQL> drop restore point before_test1;

Restore point dropped.