Using the following query one can see the flashback data available. SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME, (sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f; CURRENT_TIME OLDEST_FLASHBACK HIST_MIN —————- —————- ———- 2012-04-25 07:34 2012-04-25 05:48 106.066667
In 10g oracle has introduced the concept of recycle bin one can recover tables that were dropped. When a table is dropped, the column “DROPPED” is marked “YES” in the view user_tables/all/dba_tables. USER_RECYCLEBIN – This view has dropped objects in current user’s schema Clean current user’s recycle bin SQL> purge recyclebin; Recyclebin purged. DBA_RECYCLEBIN –…
SCN_TO_TIMESTAMP(SCN) – Prints timestamp for the SCN value passed in TIMESTAMP_TO_SCN(timestamp) – Prints SCN# for the timestamp value passed in. To get the current SCN# one can query V$DATABASE and print the value of current SCN. SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN —————- 50267271275 — get the current scn using timestamp_to_scn SQL> select timestamp_to_scn(systimestamp) current_scn…
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”…
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…
