In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.

Example:
— shows flashback is off
SQL> select flashback_on from V$database;

FLASHBACK_ON
——————
NO

— declare a variable to store current SCN
SQL> variable current_scn number
— Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

— print the current scn
SQL> print current_scn;

CURRENT_SCN
———–
389907

— query the table, shows no rows
SQL> select * FROM test;

no rows selected

— insert the row and commit
SQL> insert into test values ( 1 );

1 row created.

SQL> commit;

Commit complete.

— flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);

PL/SQL procedure successfully completed.

— as expected select returned no rows
SQL> select * FROM test;

no rows selected

— disable flashback
SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

Another way of flashback can be used is as follows
SQL> select count(1) from test as of scn :current_scn;

COUNT(1)
———-
0

-- Shows rows before the change and after using “AS OF SCN..”
SQL> select *
from (select count(1) from test),
(select count(1) from test as of scn :current_scn);
COUNT(1) COUNT(1)
———- ———-
1 0

Note: If there is a truncate executed on the table then data can’t be read from the table after enabling flashback Oracle will return “ORA-01466: unable to read data – table definition has changed” on truncated table.