Flashback Table and Flashback Drop

The FLASHBACK TABLE statement enables users to get a table to a previous point in time. It provides a fast, online solution to get back a table that has been accidentally modified or deleted by a user or application. It eliminates the DBA to perform more complicated point in time recovery operations.

We should remember the thing is you must use automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo tablespace.

Now let's go for experiment.
Flashback Drop
1)SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

2)Drop the Table,
SQL> drop table test;

Table dropped.

3)Now see the Status,
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE

In fact when we issue drop table command the object resides on recycle bin. We can see the recycle bin objects from dba_recyclebin or simply,

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE 2008-04-09:12:35:17

4)Get the Table Back:

Invoke, flahsback command. Like,

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE


However if you have another table you can use rename to clause with flashback,

flashback table test to before drop rename to test2;
SQL> drop table test;

Table dropped.

SQL> flashback table test to before drop rename to test2;

Flashback complete.

SQL> desc test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER


However you can drop table permanently without staying it in recyclebin using,

drop table test2 purge;

To drop table from recyclebin, use,

purge table_name;

Flashback Table

You can also get back you present table to a previous state. Like You deleted some row and you have committed. Now you can back your data. Here is an example for your better understanding,

SQL> select * from t;
A
----------
1
100
10

SQL> insert into t values(2);

1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;
A
----------
1
100
10
2

SQL> flashback table t to timestamp systimestamp-interval '1' minute;

Flashback complete.

SQL> select * from t;

A
----------
1
100
10


Important Things:
1) objects will go to recyclebin or it will not go is based on recyclebin parameter settings.
If I set alter system set recyclebin=off then object will not go in recycle bin.

2)Dropped SYS and SYSTEM schema objects are don't go in recyclebin.

3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.

Like,

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'TEST')
AND ORIGINAL_NAME != 'TEST';

OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER

After the table is flashed-back, the indexes and triggers on the table TEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:

ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;

But exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin and so they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.

4) To do flashback enable row movement must be enabled.

alter table test enable row movement;

Related Documents:
Restore Old Data from Flashback Query

Oracle Flashback Technology