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.

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


— 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;


— 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;


-- 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);
———- ———-
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.


One thought on “How to use DBMS_FLASHBACK?

Leave a Reply

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

You are commenting using your 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