Flashback Table feature in Oracle Database 10g helps to
reverses the effects of a DROP
TABLE operation. Flashback Drop is faster or easier than any
other recovery mechanism
that can be used in that situation.
For example:
This statement places the EMPLOYEE_DEMO table, along with
any indexes, constraints,
or other dependent objects listed previously, in the recycle
bin:
SQL> DROP TABLE EMPLOYEE_DEMO;
Table Dropped
Status after Drop:
SQL> Select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
The dropped table EMPLOYEE_DEMO, instead of completely
disappearing, was renamed to a system-defined name. It stays in the same
tablespace with the same structure as that of the original table. If there are
indexes or triggers defined on the table, they are renamed too, using the same
naming convention used by the table. Any dependent sources such as procedures
are invalidated; the triggers and indexes of the original table are instead
placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the
complete object structure of the dropped table.
View the object in Recycle Bin:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP
TIME
—————————— —————————— ———— ——————
EMPLOYEE_DEMO BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0
TABLE 2012-05-01:20:17:22
SQL> SELECT object_name as recycle_name, original_name,
type FROM recyclebin;
RECYCLE_NAME
ORIGINAL_NAME TYPE
-------------------------------- ---------------------
----------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0
EMPLOYEE_DEMO TABLE
BIN$JKS983293M1dsab4gsz/I249==$0
I_EMP_DEMO
INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0
LOB_EMP_DEMO LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0 LOB_I_EMP_DEMO
LOB INDEX
This shows the original name of the table, EMPLOYEE_DEMO, as
well as the new name in the recycle bin, which has the same name as the new
table we saw created after the drop.
Note: the exact name may differ by platform.
Reverse the Drop command:
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0"
TO BEFORE DROP;
SQL> FLASHBACK TABLE EMPLOYEE_DEMO TO BEFORE DROP;
FLASHBACK COMPLETE.
Assigning new name to the Flashbacked Table:
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE
DROP RENAME TO int2_admin_emp;
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
EMPLOYEE_DEMO TABLE
The table is reversed to its proper place. If you check the
recycle bin now, it will be empty. Dropping a table will not directly free up
space in the original tablespace. To free the space you need to purge it.
Completely drop or purging:
SQL>PURGE RECYCLEBIN;
SQL>DROP TABLE EMPLOYEE_DEMO PURGE;
SQL>PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
If you are sure that you will not want to recover a table
later, you can drop it immediately and permanently
Enabling and Disabling the Recycle Bin:
ALTER SESSION SET RECYCLEBIN=OFF;
ALTER SESSION SET RECYCLEBIN=OFF;
Either you can use it directly into the Pfile RECYCLEBIN=OFF
Restriction on Flashback Drop:
· There is no fixed amount of space pre-allocated for the
recycle bin. Therefore, there is no guaranteed minimum amount of time during
which a dropped object will remain in the
· Recycle bin. Dropped objects are kept in the recycle bin
until such time as no new extents can be allocated in the tablespace to which
the objects belong without growing the tablespace.
· You can perform Flashback Query on tables in the recycle
bin, but only by using the recycle bin name. You cannot use the original name
of the table.
· The recycle bin functionality is only available for
non-system, locally managed tablespaces.
· It is possible, however, that some dependent objects such as
indexes may have been reclaimed due to space pressure. In such cases, the
reclaimed dependent objects are not retrieved from the recycle bin.
· The recycle bin does not preserve referential constraints on
a table. If a table had referential constraints before it was dropped then
recreate any referential constraints after you retrieve the table from the
recycle bin with Flashback Drop.
· Tables which have Fine-Grained Auditing (FGA) and Virtual
Private Database (VPD) policies defined over them are not protected by the
recycle bin.
· Partitioned index-organized tables are not protected by the
recycle..
