How to use DBMS_FLASHBACK?

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.

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.

Information on flashback feature in 10g

One of the features in 10g is flashback which allows to restores tables that were dropped. Note: If an index is dropped but not a table it can’t be restored from flashback but if a table is dropped that has an index you can restore the table and the index from flashback.

The example below shows a case where table is dropped that has an index.
– create table
SQL> create table test ( x number(1) constraint test not null);

Table created.

– create index on the table test
SQL> create index test_idx on test(x);

Index created.

– show recyclebin it’s empty
SQL> show recyclebin;

SQL> column object_name format a30

– show objects
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST_IDX
TEST

– drop table
SQL> drop table test;

Table dropped.

– show recyclebin which only shows the table
SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$WLAh2JvbQDDgQ6wbCh1AMA==$0 TABLE 2008-10-07:13:58:17

– flashback the table
SQL> flashback table test to before drop;

Flashback complete.

– shows the object is restored and index too but it has name from the recycle-bin
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– show indexes it still has the name from the recyle-bin
SQL> select index_name from user_indexes;

INDEX_NAME
——————————
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– shows the index is linked to the table
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– restore the index name back
SQL> ALTER INDEX “BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0″ RENAME TO test_idx;

Index altered.

– shows the updated index name
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST TEST_IDX

Here is second example showing index is not part of recyclebin when dropped.
SQL> create table test ( x number(1) constraint test not null);

Table created.

SQL> create index test_idx on test(x);

Index created.

SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————

TEST
TEST_IDX

SQL> show recyclebin;
SQL> select * FROM user_recyclebin;

no rows selected

SQL> drop index test_idx;

Index dropped.

SQL> select * FROM user_recyclebin;

no rows selected

SQL> show recyclebin;
SQL> select index_name from user_indexes;

no rows selected