how to recover dropped table

Posted on 2007-12-02
Medium Priority
Last Modified: 2013-12-19
      I am working with Oracle 10gR2 on HP-UX 11. I have accidentally dropped a user table from my database ( a test database) and now i want to recover it. I dont have any EXPORT backup of either the database (full) or just the table. Neither do i have a RMAN backup of the database. I have some naive questions regarding this scenario:

1. Suppose if i am running Oracle 9iR2 and i just have a RMAN backup of the database. I do not have any EXPORT backup. How can i go ahead recovering the dropped table?? Is the Full recover just before the dropping only option of recovering it??

2. If I am using Oracle 10gR2 and i have just a RMAN backup of the database, in this case
        2.a Can i recover just the dropped table using this RMAN backup??
        2.b There is a option of Flash Recovery Area in Oracle 10gR2. If i set it ON, then i know i can recover the dropped table using flashback logs. If this option is set to OFF, how can i recover the dropped table??

3. Oracle 10gR2 has the RECYCLE BIN option. Any table that has been dropped will be stored in this BIN. Now
      3.a For how much time will this data reside in the RECYCLE BIN?? Is there any retention period for this data in the BIN??
     3.b  Has the RECYCLE BIN got anything to do with the Flash Recovery Area?

Any information will be of a great help.

Question by:harris2107
LVL 28

Accepted Solution

Naveen Kumar earned 600 total points
ID: 20393644
if your table name is EMP then,


Also refer to url for more details:

LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393653
any dropped table will be in recyclebin until you/DBA purge the recyclebin .

select * from recyclebin;

This will show all the objects which are in the recyclebin for the logged in user.

if you want to see for all users, then

select * from dba_recyclebin;

LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393663
The recyclebin is only available from 10g onwards. so if your version is less than this, then we may have to try to take it from export dump files or cold/hot backup.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 48

Expert Comment

ID: 20394660
You said you have RMAN backup of the DB.

Create another instance and recover the database using RMAN.

After that export/import the dropped table.
Of course it will be recovered to the time
you made the RMAN backup.

Assisted Solution

IanStuart earned 600 total points
ID: 20455749
Hello Harris,

Question 1 (V9i): Easiest way would be to restore and recover the database to another destination, then export the table and import it in the other Database.

Question 2 (V10g RMAN Only):a) I'd suggest to let RMAN perform a Tablespae Point in Time Recovery, so you'll not have the overhead to restore/recover the whole instance..
b) Yes, you could recover just the table with flashback.

Question 3 (V10 Recyclebin):
a) The objects will be in the recycle bin as long as the space isn't reclaimed by another object. So if you got plenty of space in your tablespace Oracle tries to hold the objects in the bin as long as possible.
b) No

Expert Comment

ID: 20936942
Forced accept.

Community Support Moderator

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question