Oracle Guide: Recovering accidentally dropped tables!!

 Author: Arup Nanda

Page: 1 2

More often a scenario happens when database administrator or user accidentally drops a very important table in the database and it needs to be revived as soon as possible. In this article I will give some great tips that will help you restore that accidentally dropped database table.

Flashback Query option prior to Oracle 10g:

Oracle9i Database introduced the concept of a Flashback Query option. Flashback Query option can be used to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table.

The only recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. However this requires effort as well as precious time.

Flashback Table Feature in Oracle 10g:

The Flashback Table feature in Oracle Database 10g has made the recovery of a dropped table as easy as the execution of a few statements.

Recovering an accidentally dropped table:

Consider tables in the present schema.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------ ------------ -------------------
RECYCLETEST TABLE

Now, we accidentally drop the table:

SQL> drop table recycletest;

Table dropped.

Now we check the status of the table.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

The results show that the table RECYCLETEST is gone but note the presence of the new table BIN$04LhcpndanfgMAAAAAANPw==$0.

The dropped table RECYCLETEST, instead of completely disappearing, is 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.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Step by Step Guide to Oracle Parsing
   DBA Tips for Troubleshooting Oracle Orphaned Sessions!!
   Tricky Password Features in Oracle 11g!!
   Improve performance by Optimizing SQL!!