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

What is Row Movement and how is it used?

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWIDs.

Features such as flashback table depends on row movement being enabled on the table.

To enable row movement on an existing table:

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMEMENT;

Using enable row movement can corrupt any Oracle features that rely on ROWID, such as nested tables.
Check for such objects before you enable row movement:

SELECT COUNT(*)
FROM dba_nested_tables
WHERE owner = 'SCOTT';


Sources: Oracle Documentation and Don Burleson

How to use flashback table

Use a point-in-time in the past:

FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
TO_TIMESTAMP('09-12-2008 14:00:00', 'DD-MM-YYYY HH24:MI:SS');

Notice the peculiar syntax:

"TO TIMESTAMP TO_TIMESTAMP..."

Use a specific SCN:

FLASHBACK TABLE SCOTT.EMP TO SCN 144003819;

If you want to flash back multiple tables, you can use many table names separated by a comma as follows:

FLASHBACK TABLE SCOTT.EMP, SCOTT.DEPT TO SCN 144003819;

Use a restore point:

FLASHBACK TABLE SCOTT.EMP TO RESTORE POINT BEFORE_MAINTENANCE;

Flashback to before drop:

FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP;

You can add the RENAME TO clause to rename the retrieved table:

FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP RENAME TO EMP_OLD;

Note:
For Oracle Flashback Table to succeed, the system must retain enough undo information to satisfy the specified SCN or timestamp, and the integrity constraints specified on the tables cannot be violated. Also, row movement must be enabled on the table.

Row movement must be enabled for the table you attempt to flash back. It can be switched on right before you use the flashback table feature. If you are flashing back to a time when row movement was *not* enabled, that is no problem, you can still use flashback table, as long as row movment is enabled *at the time* of executing the flashback table command.

There is one important exception to the requirement that row movement must be enabled: flashing back table TO BEFORE DROP. Such an operation Oracle calls a "flashback drop operation", and it uses dropped data in the recyclebin rather than undo data.

You can check the content of the recyclebin before you flash back a table to before it was dropped, like this:

select owner,object_name,original_name,operation,droptime,can_undrop from dba_recyclebin where original_name='EMP';

OWNER                          OBJECT_NAME                    ORIGINAL_NAME                  OPERATION DROPTIME            CAN
------------------------------ ------------------------------ ------------------------------ --------- ------------------- ---
SCOTT                  BIN$StmkAPSYpxbgUzwGAQph5w==$0 EMP               DROP      2017-03-16:14:00:09 YES

There are some restrictions to flashback table. Read about them here "Notes on Flashing Back Dropped Tables"