Flashback Table in Oracle Database

What happens when we drop a table in Oracle 10g?

Whenever we drop a table in Oracle 10g, internally Oracle will rename the table and all the associated objects like index, constraints etc with a prefix of “BIN$” and stores in the recyclebin. We can either read the data directly from the recyclebin or can be restored as long as it is present in the recyclebin.

Two questions may arise at this stage.

1) How to restore the data ?

2) How long will the data be present in the recyclebin?

Lets understand more on this with the below example. The point to be noted here is that the whole concept is based on the FLASHBACK TABLE feature of Oracle Database.

SQL> drop table test_tab;
Table dropped

SQL> select OBJECT_NAME, ORIGINAL_NAME,
DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
BIN$dD1ZsEvnDTHgRAADujKrxQ==$0 TEST_TAB 2010-09-23:12:40:58

The table test_tab is renamed as “BIN$dD1ZsEvnDTHgRAADujKrxQ==$0”

SQL> select * from “BIN$dD1ZsEvnDTHgRAADujKrxQ==$0”;
COL1 COL2
1 23-SEP-10

SQL> flashback table TEST_TAB to before drop;
Flashback complete.

SQL>select OBJECT_NAME,ORIGINAL_NAME,DROPTIME from recyclebin;

no rows selected

SQL>select OBJECT_NAME,ORIGINAL_NAME,DROPTIME from
recyclebin;
no rows selected

SQL> select * from test_tab;
COL1 COL2
1 23-SEP-10

Here comes the answer to the second question.
Oracle will hold the objects in the recyclebin until the tablespace becomes full or the user quota on the tablespace exceeds. At that point, oracle will purge the objects on a ‘first in – first out’ basis just to occupy the space required for the current transaction. Oracle will follow this option, even if the datafiles are in AUTOEXTEND ON mode.
Also, we have options to drop a table without getting stored in the recyclebin ( similar to shift+delete in windows) and to drop objects from the recyclebin . Both can be done using “PURGE” command.

Research and Article Contribution by : Anju