Use the dbms_flashback to set your session back in time

A convenient way to set your session back in time, is to execute the dbms_flashback.enable_at_time procedure.
This way you can work with your data as they were at a previous point-in-time, given that your UNDO settings support it.

Oracle states

"The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at earlier times."



A simple test of the procedure follows.

First, the user would need execute privileges on the package in which enable_at_time belongs, the dbms_flashback package:

connect sys/passwd as sysdba
grant execute on dbms_flashback to scott;

SQL>connect scott/passwd 
SQL>select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

SQL>

Make a change:

SQL> insert into album (album_id, album_title,artist_id) values(200,'Whiplash Smile', 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
       200 Whiplash Smile                                             10
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

To see the content of the table album as it was 5 minutes ago:

SQL> execute dbms_flashback.enable_at_time(sysdate-5/1440);

PL/SQL procedure successfully completed.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

Disable the flashback setting:

SQL> execute dbms_flashback.disable();

PL/SQL procedure successfully completed.

If you want to use flashback queries against other schemas' tables, you need object privileges to do so:

grant flashback on scott.album to jim;

Alternatively, you can grant jim the system privilege flashback any table:

connect / as sysdba
grant flashback any table to jim;

Sources: Oracle Documentation This document from Oracle Support