How to see the oldest flashback available?

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 Advertisements Continue reading How to see the oldest flashback available?

RECYLE BIN

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 – Has dropped objects in all users # purge all tables … Continue reading RECYLE BIN

How to convert SCN to timestamp and viceversa?

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 from dual; CURRENT_SCN —————- 50267273738 If the value passed in … Continue reading How to convert SCN to timestamp and viceversa?

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 … Continue reading How to use DBMS_FLASHBACK?

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 … Continue reading Information on flashback feature in 10g