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

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 in the recycle bin
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

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 too old, you will get the following message “ORA-08180: Time specified is too old.”

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.

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.

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